Chapter 10, “Userforms—An Introduction,” covered the basics of adding controls to userforms. This chapter continues on this topic by looking at more advanced controls and methods for making the most out of userforms.
In the VB Editor, hidden under the View menu in the Toolbars command are a few toolbars that don’t appear unless the user intervenes. One of these is the UserForm toolbar, shown in Figure 23.1.
Chapter 10 began a review of some of the controls available on userforms. The review is continued here. At the end of each control review is a table listing that control’s events.
 | Check boxes allow the user to select one or more options on a userform. Unlike the option buttons we discussed in Chapter 10, a user can select one or more check boxes at a time. |
The value of a checked box is True; the value of an unchecked box is False. If you clear the value of a check box (Checkbox1.value = ""), when the userform runs, the check box will have a faded check in it, as shown in Figure 23.2. This could be useful to verify that users have viewed all options and made a selection.
The following code reviews all the check boxes in the language group and, if a value is null, prompts the user to review the selections:
Private Sub btnClose_Click()
Dim Msg As String
Dim Chk As Control
Set Chk = Nothing
'narrow down the search to just the 2nd page's controls
For Each Chk In frm_Multipage.MultiPage1.Pages(1).Controls
'only need to verify checkbox controls
If TypeName(Chk) = "CheckBox" Then
'and just in case we add more check box controls,
'just check the ones in the group
If Chk.Object.GroupName = "Languages" Then
'if the value is null (the property value is empty)
If IsNull(Chk.Object.Value) Then
'add the caption to a string
Msg = Msg & vbNewLine & Chk.Caption
End If
End If
End If
Next Chk
If Msg <> "" Then
Msg = "The following check boxes were not verified:" & vbNewLine & Msg
MsgBox Msg, vbInformation, "Additional Information Required"
End If
End SubTable 23.1 lists the events for CheckBox controls.
Table 23.1. Events for CheckBox Controls
Event | Description |
|---|---|
| Occurs after a check box has been checked/unchecked. |
| Occurs while the user drags and drops data onto the check box. |
| Occurs right before the user is about to drop or paste data onto the check box. |
| Occurs before the check box is checked/unchecked. |
| Occurs when the value of the check box is changed. |
| Occurs when the user clicks the control with the mouse. |
| Occurs when the user double-clicks the check box with the mouse. |
| Occurs right before the check box receives the focus from another control on the same userform. |
| Occurs when the check box runs into an error and can’t return the error information. |
| Occurs right after the check box loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the check box. |
| Occurs when the user moves the mouse within the borders of the check box. |
| Occurs when the user releases the mouse button within the borders of the check box. |
 | The |
To learn more about |
By default, a tab strip is thin, with two tabs at the top. Right-clicking a tab enables you to add, remove, rename, or move a tab. The tab strip should also be sized to hold all the controls. A button for closing the form should be drawn outside the tab strip area.
The tabs can also be moved around the strip. This is done by changing the TabOrientation property. The tabs can be at the top, bottom, left, or right side of the userform.
The following lines of code were used to create the tab strip form shown in Figure 23.3. The Initialize sub calls the sub SetValuestoTabStrip, which sets the value for the first tab:
Private Sub UserForm_Initialize() SetValuesToTabStrip 1 'As default End Sub
These lines of code handle what happens when a new tab is selected.
Private Sub TabStrip1_Change() Dim lngRow As Long lngRow = TabStrip1.Value + 1 SetValuesToTabStrip lngRow End Sub
This sub provides the data shown on each tab. A sheet was set up, with each row corresponding to a tab.
Private Sub SetValuesToTabStrip(ByVal lngRow As Long)
With frm_Staff
.lbl_Name.Caption = Cells(lngRow, 2).Value
.lbl_Phone.Caption = Cells(lngRow, 3).Value
.lbl_Fax.Caption = Cells(lngRow, 4).Value
.lbl_Email.Caption = Cells(lngRow, 5).Value
.lbl_Website.Caption = Cells(lngRow, 6).Value
.Show
End With
End SubThe tab strip’s values are automatically filled in. They correspond to the tab’s position in the strip; moving a tab changes its value.
Tip
If you want a single tab to have an extra control, the control could be added at runtime when the tab became active and removed when the tab is deactivated.
Table 23.2 lists the events for the TabStrip control.
Table 23.2. Events for TabStrip Controls
Event | Description |
|---|---|
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user drops or pastes data into the control. |
| Occurs when the value of the control is changed. |
| Occurs when the user clicks the control with the mouse. |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
 | The |
The form in Figure 23.4 and the following code allow the user to select a range, which is then made bold.
Private Sub cb1_Click() Range(RefEdit1.Value).Font.Bold = True End Sub
Table 23.3 lists the events for RefEdit controls.
Table 23.3. Events for RefEdit Controls
Event | Description |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user drops or pastes data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
| Occurs when the user clicks the control with the mouse. |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs when the drop-down list appears by pressing on the drop-down arrow of the combo box or press F4 on the keyboard. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
 | A toggle button looks like a normal command button, but when the user presses it, it stays pressed until it’s selected again. This allows a |
Table 23.4. Events for ToggleButton Controls
Event | Description |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user drops or pastes data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
| Occurs when the user clicks the control with the mouse. |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
 | Chapter 10 discussed using a |
The userform shown in Figure 23.5 includes a label named Label1 and a scrollbar called ScrollBar1.
The userform’s Initialize code sets up the Min and Max values for the scrollbar. It initializes the scrollbar to a value from cell A1 and updates the Label1.Caption:
Private Sub UserForm_Initialize()
Me.ScrollBar1.Min = 0
Me.ScrollBar1.Max = 100
Me.ScrollBar1.Value = Range("A1").Value
Me.Label1.Caption = Me.ScrollBar1.Value
End SubTwo event handlers are needed for the scrollbar. The Change event handles if users click the arrows at the ends of the scrollbar. The Scroll event handles if they drag the slider to a new value:
Private Sub ScrollBar1_Change()
' This event handles if they touch
' the arrows on the end of the scrollbar
Me.Label1.Caption = Me.ScrollBar1.Value
End Sub
Private Sub ScrollBar1_Scroll()
' This event handles if they drag the slider
Me.Label1.Caption = Me.ScrollBar1.Value
End SubFinally, the event attached to the button writes the scrollbar value out to the worksheet:
Private Sub CommandButton1_Click()
Range("A1").Value = Me.ScrollBar1.Value
Unload Me
End SubTable 23.5 lists the events for Scrollbar controls.
Table 23.5. Events for Scrollbar Controls
Description | |
|---|---|
| Occurs after the control’s data has been changed by the user. |
| Occurs while the user drags and drops data onto the control. |
| Occurs right before the user drops or pastes data into the control. |
| Occurs before the data in the control is changed. |
| Occurs when the value of the control is changed. |
| Occurs when the user clicks the control with the mouse. |
| Occurs when the user double-clicks the control with the mouse. |
| Occurs when the drop-down list appears by pressing on the drop-down arrow of the combo box or pressing F4 on the keyboard. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and can’t return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the user presses a key on the keyboard. |
| Occurs when the user presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the user releases a key on the keyboard. |
| Occurs when the user presses the mouse button within the borders of the control. |
| Occurs when the user moves the mouse within the borders of the control. |
| Occurs when the user releases the mouse button within the borders of the control. |
In Chapter 22, “Creating Classes, Records, and Collections,” several labels on a sheet were grouped together into a collection. With a little more code, these labels were turned into help screens for the users. Userform controls can be grouped into collections, too, and take advantage of class modules.
The following example checks or unchecks all the check boxes on the userform, depending on which label the user chooses.
Place the following code in the class module, clsFormEvents. It consists of one property, chb, and two methods, SelectAll and UnselectAll.
The SelectAll method places a check in a check box by setting its value to True:
Option Explicit Public WithEvents chb As MSForms.CheckBox Public Sub SelectAll() chb.Value = True End Sub
The UnselectAll method removes the check from the check box:
Public Sub UnselectAll() chb.Value = False End Sub
That sets up the class module. Next, the controls need to be placed in a collection. The following code, placed behind the form, frm_Movies, places the check boxes into a collection. The check boxes are part of a frame, f_Selection, which makes it easier to create the collection because it narrows down the number of controls that need to be checked from the entire userform to just those controls within the frame:
Option Explicit
Dim col_Selection As New Collection
Private Sub UserForm_Initialize()
Dim ctl As MSForms.CheckBox
Dim chb_ctl As clsFormEvents
' Go thru the members of the frame and add them to the collection
For Each ctl In f_Selection.Controls
Set chb_ctl = New clsFormEvents
Set chb_ctl.chb = ctl
col_Selection.Add chb_ctl
Next ctl
End SubWhen the form is opened, the controls are placed into the collection. All that’s left now is to add the code for labels to select and unselect the check boxes:
Private Sub lbl_SelectAll_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.SelectAll
Next ctl
End SubThe following code unselects the check boxes in the collection:
Private Sub lbl_unSelectAll_Click()
Dim ctl As clsFormEvents
For Each ctl In col_Selection
ctl.Unselectall
Next ctl
End SubAll the check boxes can be checked and unchecked with a single click of the mouse, as shown in Figure 23.6.
Tip
If your controls can’t be placed in a frame, you can use a tag to create an improvised grouping. A tag is a property that holds more information about a control. Its value is of type string, so it can hold any type of information. For example, it can be used to create an informal group of controls from different groupings.
Ever had a userform active but needed to look at something on a sheet? There was a time when the form had to be shut down before anything else in Excel could be done. No longer! Forms can now be modeless, which means they don’t have to interfere with the functionality of Excel. The user can type in a cell, switch to another sheet, copy/paste data, and use toolbars and menus—it is as if the userform were not there.
By default, a userform is modal, which means that there is no interaction with Excel other than the form. To make the form modeless, change the ShowModal property to False. After it is modeless, the user can select a cell on the sheet while the form is active, as shown in Figure 23.7.
In the userform example shown in Figure 23.3, there is a field for email and a website address. Wouldn’t it be nice to be able to click these and have a blank email message or web page automatically appear? You can! The following program creates a new message or opens a web browser when the corresponding label is clicked.
The application programming interface (API) declaration, and any other constants, go at the very top of the code.
Private Declare Function ShellExecute Lib "shell32.dll" Alias _
"ShellExecuteA"(ByVal hWnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SWNormal = 1This sub controls what happens when the email label is clicked, as shown in Figure 23.8:
Private Sub lbl_Email_Click()
Dim lngRow As Long
lngRow = TabStrip1.Value + 1
ShellExecute 0&, "open", "mailto:" & Cells(lngRow, 5).Value, _
vbNullString, vbNullString, SWNormal
End Sub
This sub controls what happens when the website label is clicked:
Private Sub lbl_Website_Click()
Dim lngRow As Long
lngRow = TabStrip1.Value + 1
ShellExecute 0&, "open", Cells(lngRow, 6).Value, vbNullString, _
vbNullString, SWNormal
End SubIt is possible to add controls to a userform at runtime. This is convenient if you are not sure how many items you will be adding to the form.
Figure 23.9 shows a plain form. It has only one button. This plain form is used to display any number of pictures from a product catalog. The pictures and accompanying labels appear at runtime, as the form is being displayed.
A sales rep making a sales presentation uses this form to display a product catalog. He can select any number of SKUs from an Excel worksheet and press a hot key to display the form. If he selects 20 items on the worksheet, the form displays with each picture fairly small, as shown in Figure 23.10.
If the sales rep selects fewer items, the images are displayed larger, as shown in Figure 23.11.
A number of techniques are used to create this userform on-the-fly. The initial form contains only one button, called cbClose. Everything else is added on-the-fly.
One goal is to give the best view of the images in the product catalog. This means having the form appear as large as possible. The following code uses the form’s Height and Width properties to make sure the form fills almost the entire screen:
' resize the form Me.Height = Int(0.98 * ActiveWindow.Height) Me.Width = Int(0.98 * ActiveWindow.Width)
For a normal control added at design time, it is easy to refer to the control by using its name:
Me.cbSave.Left = 100
But, for a control that is added at runtime, you have to use the Controls collection to set any properties for the control. Therefore, it is important to set up a variable to hold the name of the control. Controls are added with the .Add method. The important parameter is the bstrProgId. This code name dictates whether the added control is a label, text box, command button, or something else.
The following code adds a new label to the form. PicCount is a counter variable used to ensure that each label has a new name. After the form is added, specify a position for the control by setting the Top and Left properties. You should also set a Height and Width for the control:
LC = "LabelA" & PicCount Me.Controls.Add bstrProgId:="forms.label.1", Name:=LC, Visible:=True Me.Controls(LC).Top = 25 Me.Controls(LC).Left = 50 Me.Controls(LC).Height = 18 Me.Controls(LC).Width = 60 Me.Controls(LC).Caption = cell.value
Caution
You lose some of the AutoComplete options with this method. Normally, if you would start to type Me.cbClose., the AutoComplete options would present the valid choices for a command button. However, when you use the Me.Controls(LC) collection to add controls on-the-fly, VBA doesn’t know what type of control is referenced. In this case, it is helpful to know you need to set the Caption property rather than the Value property for a label.
In reality, you need to be able to calculate values for Top, Left, Height, and Width on the fly. You would do this based on the actual height and width of the form and on how many controls are needed.
To add other types of controls, change the ProgId used with the Add method. Table 23.6 shows the ProgIds for various types of controls.
Table 23.6. Userform Controls and Corresponding ProgIds
Control |
|
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
There is some unpredictability in adding images. Any given image might be shaped either landscape or portrait. The image might be small or huge. The strategy you might want to use is to let the image load full size by setting the .AutoSize parameter to True before loading the image:
TC = "Image" & PicCount Me.Controls.Add bstrProgId:="forms.image.1", Name:=TC, Visible:=True Me.Controls(TC).Top = LastTop Me.Controls(TC).Left = LastLeft Me.Controls(TC).AutoSize = True On Error Resume Next Me.Controls(TC).Picture = LoadPicture(fname) On Error GoTo 0
Then, after the image has loaded, you can read the control’s Height and Width properties to determine whether the image is landscape or portrait and whether the image is constrained by available width or available height:
' The picture resized the control to full size
' determine the size of the picture
Wid = Me.Controls(TC).Width
Ht = Me.Controls(TC).Height
WidRedux = CellWid / Wid
HtRedux = CellHt / Ht
If WidRedux < HtRedux Then
Redux = WidRedux
Else
Redux = HtRedux
End If
NewHt = Int(Ht * Redux)
NewWid = Int(Wid * Redux)After you find the proper size for the image to have it draw without distortion, you can set the AutoSize property to False and use the correct height and width to have the image not appear distorted:
' Now resize the control Me.Controls(TC).AutoSize = False Me.Controls(TC).Height = NewHt Me.Controls(TC).Width = NewWid Me.Controls(TC).PictureSizeMode = fmPictureSizeModeStretch
This is the complete code for the Picture Catalog userform:
Private Sub UserForm_Initialize()
' Display pictures of each SKU selected on the worksheet
' This may be anywhere from 1 to 36 pictures
PicPath = "C:qimageqi"
Dim Pics ()
' resize the form
Me.Height = Int(0.98 * ActiveWindow.Height)
Me.Width = Int(0.98 * ActiveWindow.Width)
' determine how many cells are selected
' We need one picture and label for each cell
CellCount = Selection.Cells.Count
ReDim Preserve Pics(1 To CellCount)
' Figure out the size of the resized form
TempHt = Me.Height
TempWid = Me.Width
' The number of columns is a roundup of SQRT(CellCount)
' This will ensure 4 rows of 5 pictures for 20, etc.
NumCol = Int(0.99 + Sqr(CellCount))
NumRow = Int(0.99 + CellCount / NumCol)
' Figure out the ht and wid of each square
' Each column will have 2 pts to left & right of pics
CellWid = Application.WorksheetFunction.Max(Int(TempWid / NumCol) - 4, 1)
' each row needs to have 33 points below it for the label
CellHt = Application.WorksheetFunction.Max(Int(TempHt / NumRow) - 33, 1)
PicCount = 0 ' Counter variable
LastTop = 2
MaxBottom = 1
' Build each row on the form
For x = 1 To NumRow
LastLeft = 3
' Build each column in this row
For Y = 1 To NumCol
PicCount = PicCount + 1
If PicCount > CellCount Then
' There are not an even number of pictures to fill
' out the last row
Me.Height = MaxBottom + 100
Me.cbClose.Top = MaxBottom + 25
Me.cbClose.Left = Me.Width - 70
Repaint
Exit Sub
End If
ThisStyle = Selection.Cells(PicCount).Value
ThisDesc = Selection.Cells(PicCount).Offset(0, 1).Value
fname = PicPath & ThisStyle & ".jpg"
TC = "Image" & PicCount
Me.Controls.Add bstrProgId:="forms.image.1", Name:=TC, Visible:=True
Me.Controls(TC).Top = LastTop
Me.Controls(TC).Left = LastLeft
Me.Controls(TC).AutoSize = True
On Error Resume Next
Me.Controls(TC).Picture = LoadPicture(fname)
On Error GoTo 0
' The picture resized the control to full size
' determine the size of the picture
Wid = Me.Controls(TC).Width
Ht = Me.Controls(TC).Height
WidRedux = CellWid / Wid
HtRedux = CellHt / Ht
If WidRedux < HtRedux Then
Redux = WidRedux
Else
Redux = HtRedux
End If
NewHt = Int(Ht * Redux)
NewWid = Int(Wid * Redux)
' Now resize the control
Me.Controls(TC).AutoSize = False
Me.Controls(TC).Height = NewHt
Me.Controls(TC).Width = NewWid
Me.Controls(TC).PictureSizeMode = fmPictureSizeModeStretch
Me.Controls(TC).ControlTipText = "Style " & _
ThisStyle & " " & ThisDesc
' Keep track of the bottom-most & right-most picture
ThisRight = Me.Controls(TC).Left + Me.Controls(TC).Width
ThisBottom = Me.Controls(TC).Top + Me.Controls(TC).Height
If ThisBottom > MaxBottom Then MaxBottom = ThisBottom
' Add a label below the picture
LC = "LabelA" & PicCount
Me.Controls.Add bstrProgId:="forms.label.1", Name:=LC, Visible:=True
Me.Controls(LC).Top = ThisBottom + 1
Me.Controls(LC).Left = LastLeft
Me.Controls(LC).Height = 18
Me.Controls(LC).Width = CellWid
Me.Controls(LC).Caption = "Style " & ThisStyle & " " & ThisDesc
' Keep track of where the next picture should display
LastLeft = LastLeft + CellWid + 4
Next Y ' end of this row
LastTop = MaxBottom + 21 + 16
Next x
Me.Height = MaxBottom + 100
Me.cbClose.Top = MaxBottom + 25
Me.cbClose.Left = Me.Width - 70
Repaint
End SubYou’ve designed a great userform, but now there’s just one thing missing—guidance for the users. The following sections show you four ways you can help users fill out the form properly.
Built-in forms often have keyboard shortcuts that allow actions to be triggered or fields selected with a few keystrokes. These shortcuts are identified by an underlined letter on a button or label.
You can add this same capability to custom userforms by entering a value in the Accelerator property of the control. Alt + the accelerator key selects the control. For example, in Figure 23.12, Alt+H checks the VHS check box. Repeating the combination unchecks the box.
When a cursor is waved over a toolbar, tip text appears, hinting at what the control does. You can also add tip text to userforms by entering a value in the ControlTipText property of a control. In Figure 23.13, tip text has been added to the frame surrounding the various categories.
Users can also tab from one field to another. This is an automatic feature in a form. To control which field the next tab brings a user to, you can set the TapStop property value for each control.
The first tab stop is zero, and the last tab stop is equal to the number of controls in a group. Remember, a group can be created with a frame. Excel does not allow multiple controls to have the same tab stop. After tab stops are set, the user can use the Tab key and spacebar to select/deselect various options, as shown in Figure 23.14.
Another method for helping a user fill out a form is to color the active field. The following example changes the color of a text box or combo box when it is active.
Place the following in a class module called clsCtlColor:
Public Event GetFocus()
Public Event LostFocus(ByVal strCtrl As String)
Private strPreCtr As String
Public Sub CheckActiveCtrl(objForm As MSForms.UserForm)
With objForm
If TypeName(.ActiveControl) = "ComboBox" Or _
TypeName(.ActiveControl) = "TextBox" Then
strPreCtr = .ActiveControl.Name
On Error GoTo Terminate
Do
DoEvents
If .ActiveControl.Name <> strPreCtr Then
If TypeName(.ActiveControl) = "ComboBox" Or _
TypeName(.ActiveControl) = "TextBox" Then
RaiseEvent LostFocus(strPreCtr)
strPreCtr = .ActiveControl.Name
RaiseEvent GetFocus
End If
End If
Loop
End If
End With
Terminate:
Exit Sub
End SubPlace the following behind the userform:
Private WithEvents objForm As clsCtlColor Private Sub UserForm_Initialize() Set objForm = New clsCtlColor End Sub
This sub changes the BackColor of the active control when the form is activated:
Private Sub UserForm_Activate() If TypeName(ActiveControl) = "ComboBox" Or _ TypeName(ActiveControl) = "TextBox" Then ActiveControl.BackColor = &HC0E0FF End If objForm.CheckActiveCtrl Me End Sub
This sub changes the BackColor of the active control when it gets the focus:
Private Sub objForm_GetFocus() ActiveControl.BackColor = &HC0E0FF End Sub
This sub changes the BackColor back to white when the control loses the focus:
Private Sub objForm_LostFocus(ByVal strCtrl As String) Me.Controls(strCtrl).BackColor = &HFFFFFF End Sub
This sub clears the objForm when the form is closed:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Set objForm = Nothing End Sub
Multicolumn List Boxes
You’ve created several spreadsheets containing store data. The primary key of each set is the store number. The workbook is used by several people, but not everyone memorizes stores by his or her store numbers. You need some way of letting a user select a store by its name, but at the same time, return the store number to be used in the code. You could use VLOOKUP or MATCH, but there’s another way.
A list box can have more than one column, but not all the columns need to be visible to the user. Also, the user can select an item from the visible list, but the list box returns the corresponding value from another column.
Draw a list box and set the ColumnCount property to 2. Set the RowSource to a two-column range called Stores. The first column of the range is the store number; the second column is the store name. At this point, the list box is displaying both columns of data. To change this, set the column width to 0, 20—the text automatically updates to 0 pt;20 pt. The first column is now hidden. Figure 23.15 shows the list box properties as they need to be.
The appearance of the list box has now been set. When the user activates the list box, she will see only the store names. To return the value of the first column, set the BoundColumn property to 1. This can be done through the Properties window or through code. This example uses code to maintain the flexibility of returning the store number (see Figure 23.16):
Private Sub UserForm_Initialize()
lb_StoreName.BoundColumn = 1
End Sub
Private Sub lb_StoreName_Click()
lbl_StoreNum.Caption = lb_StoreName.Value
End Sub
Ever had a form that you had to keep moving out of the way so you could see the data behind it? The following code sets the userform at a 50 percent transparency (see Figure 23.17) so that you can see the data behind without moving the form somewhere else on the screen (and blocking more data).
Place the following in the declarations section of the userform:
Private Declare Function GetActiveWindow Lib "USER32" () As Long
Private Declare Function SetWindowLong Lib "USER32" Alias _
"SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Declare Function GetWindowLong Lib "USER32" Alias _
"GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetLayeredWindowAttributes Lib "USER32" _
(ByVal hWnd As Long, ByVal crKey As Integer, _
ByVal bAlpha As Integer, ByVal dwFlags As Long) As Long
Private Const WS_EX_LAYERED = &H80000
Private Const LWA_COLORKEY = &H1
Private Const LWA_ALPHA = &H2
Private Const GWL_EXSTYLE = &HFFEC
Dim hWnd As LongPlace the following behind a userform. When the form is activated, the transparency will be set:
Private Sub UserForm_Activate() Dim nIndex As Long hWnd = GetActiveWindow nIndex = GetWindowLong(hWnd, GWL_EXSTYLE) SetWindowLong hWnd, GWL_EXSTYLE, nIndex Or WS_EX_LAYERED '50% semitransparent SetLayeredWindowAttributes hWnd, 0, (255 * 50) / 100, LWA_ALPHA End Sub
In Chapter 24, “Windows Application Programming Interface (API),” you will learn how to access functions and procedures hidden in files on your computer.