Chapter 22
Advanced userform techniques
In this chapter, you will:
Access the UserForm toolbar
Learn how to use
CheckBox,TabStrip,RefEdit, andToggleButtoncontrolsUse a collection to control multiple controls
Select a cell on a sheet while a userform is open
Use hyperlinks in userforms
Add controls at runtime
Add help to a userform
Set up a multicolumn list box
Create transparent forms
Chapter 10, “Userforms: An introduction,” covered the basics of adding controls to userforms. This chapter continues the topic, looking at more advanced controls and methods for making the most out of userforms.
Using the UserForm toolbar in the design of controls on userforms
In the VB Editor, under View, Toolbars, you’ll find a few toolbars that do not appear unless you select them. One of these is the UserForm toolbar, shown in Figure 22-1. It has functionality useful for organizing the controls you add to a userform; for example, you can use it to make all the controls you select the same size.
FIGURE 22-1 The UserForm toolbar has tools for organizing the controls on a userform.
More userform controls
The following sections cover more userform controls you can use to help obtain information from people. At the end of each of the following subsections is a table that lists that control’s events.
CheckBox controls
Check boxes allow the user to select one or more options on a userform. Unlike with the option buttons discussed in Chapter 10, a person can select one or more check boxes at a time.
The value of a selected CheckBox is True; the value of an unselected CheckBox is False. If you clear the value of a CheckBox (CheckBox1.Value = ""), when the userform runs, the check box will have a grayed-out check in it, as shown in Figure 22-2. This can be useful for verifying that users have viewed all options and made a selection.
FIGURE 22-2 Use the null value of the CheckBox to verify that a person has viewed and answered all options.
You can use code like the following to review all the check boxes in the Languages group of the dialog box shown in Figure 22-2. If a value is null, the user is prompted 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.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"
Else
Unload Me
End If
End SubTable 22-1 lists the events for CheckBox controls.
TABLE 22-1 CheckBox control events
Event | Description |
|---|---|
| Occurs after a check box has been selected/cleared. |
| Occurs while the person drags and drops data onto the check box. |
| Occurs right before the person is about to drop or paste data onto the check box. |
| Occurs before the check box is selected/cleared. |
| Occurs when the value of the check box is changed. |
| Occurs when the person clicks the control with the mouse. |
| Occurs when the person 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 cannot return the error information. |
| Occurs right after the check box loses focus to another control on the same userform. |
| Occurs when the person presses a key on the keyboard. |
| Occurs when the person presses an ANSI key. An ANSI key is a typeable character such as the letter A. |
| Occurs when the person releases a key on the keyboard. |
| Occurs when the person presses the mouse button within the borders of the check box. |
| Occurs when the person moves the mouse within the borders of the check box. |
| Occurs when the person releases the mouse button within the borders of the check box. |
TabStrip controls
The MultiPage control allows a userform to have several pages. Each page of the form can have its own set of controls, unrelated to any other control on the form. A TabStrip control also allows a userform to have many pages, but the controls on a tab strip are identical; they are drawn only once. Yet when the form is run, the information changes depending on which tab strip is active (see Figure 22-3).
Note
To learn more about MultiPage controls, see “Using the MultiPage control to combine forms” in Chapter 10.
FIGURE 22-3 A tab strip allows a userform with multiple pages to share controls but not information.
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 that tab. Size the tab strip to hold all the controls. Outside the tab strip area, draw a button for closing the form.
You can move the tabs around the strip, as shown in Figure 22-3, 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 22-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_Address.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. The value of the first tab of a tab strip is 0, which is why, in the preceding code, we add 1 to the tab strip value when the form is initialized to get it to correspond with the row on the sheet.
Tip
If you want a single tab to have an extra control, the control could be added at runtime, when the tab is activated, and removed when the tab is deactivated.
Table 22-2 lists the events for the TabStrip control.
TABLE 22-2 TabStrip control events
Event | Description |
|---|---|
| Occurs while the person drags and drops data onto the control. |
| Occurs right before the person drops or pastes data into the control. |
| Occurs when the value of the control is changed. |
| Occurs when the person clicks the control with the mouse. |
| Occurs when the person 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 cannot return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the person presses a key on the keyboard. |
| Occurs when the person presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the person releases a key on the keyboard. |
| Occurs when the person presses the mouse button within the borders of the control. |
| Occurs when the person moves the mouse within the borders of the control. |
| Occurs when the person releases the mouse button within the borders of the control. |
RefEdit controls
The RefEdit control allows a person to select a range on a sheet; the range is returned as the value of the control. You can add it to any form. When you click the button on the right side of the field, the userform disappears and is replaced with the range selection form that is used for selecting ranges with Excel’s many wizard tools, as shown in Figure 22-4. Click the button on the right of the field to show the userform once again.
FIGURE 22-4 Use RefEdit to enable a person to select a range on a sheet.
The following code used with a RefEdit control allows a person to select a range, which is then made bold:
Private Sub cb1_Click() Range(RefEdit1.Value).Font.Bold = True Unload Me End Sub
Table 22-3 lists the events for RefEdit controls.
Caution
RefEdit control events are notorious for not working properly. If you run into this problem, use a different control’s event to trigger code.
TABLE 22-3 RefEdit control events
Event | Description |
|---|---|
| Occurs after the control’s data has been changed. |
| Occurs while the person drags and drops data onto the control. |
| Occurs right before the person 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 person clicks the control with the mouse. |
| Occurs when the person double-clicks the control with the mouse. |
| Occurs when the person clicks the drop button on the right side of the field. |
| Occurs right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and cannot return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the person presses a key on the keyboard. |
| Occurs when the person presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the person releases a key on the keyboard. |
| Occurs when the person presses the mouse button within the borders of the control. |
| Occurs when the person moves the mouse within the borders of the control. |
| Occurs when the person releases the mouse button within the borders of the control. |
ToggleButton controls
A toggle button looks like a normal command button, but when it’s clicked, it stays pressed until it’s clicked again. This allows a True or False value to be returned based on the status of the button. Table 22-4 lists the events for the ToggleButton controls.
TABLE 22-4 ToggleButton control events
Event | Description |
|---|---|
| Occurs after the control’s data has been changed. |
| Occurs while the person drags and drops data onto the control. |
| Occurs right before the person 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 someone clicks the control with the mouse. |
| Occurs when the person 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 cannot return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the person presses a key on the keyboard. |
| Occurs when the person presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the person releases a key on the keyboard. |
| Occurs when the person presses the mouse button within the borders of the control. |
| Occurs when the person moves the pointer within the borders of the control. |
| Occurs when the person releases the mouse button within the borders of the control. |
Using a scrollbar as a slider to select values
![The scrollbar button in the image looks like two stacked arrows[md]one pointing up and the other pointing down.](https://imgdetail.ebookreading.net/2023/10/9780137521531/9780137521531__9780137521531__files__graphics__22UN05.jpg)
Chapter 10 discusses using a SpinButton control to enable someone to choose a date. A spin button is useful, but it enables you to adjust up or down by only one unit at a time. An alternative method is to draw a horizontal or vertical scrollbar in the middle of the userform and use it as a slider. People can use arrows on the ends of the scrollbar as they would the spin button arrows, but they can also grab the scrollbar and instantly drag it to a certain value.
The userform shown in Figure 22-5 includes a label named Label1 and a scrollbar called ScrollBar1.
FIGURE 22-5 Using a ScrollBar control allows the person to drag to a particular numeric or data value.
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 = Worksheets("Scrollbar").Range("A1").Value
Me.Label1.Caption = Me.ScrollBar1.Value
End SubTwo event handlers are needed for the scrollbar. The Change event triggers when a person clicks the arrows at the ends of the scrollbar. The Scroll event triggers when they drag the slider to a new value:
Private Sub ScrollBar1_Change()
'This event triggers when the user touches
'the arrows on the end of the scrollbar
Me.Label1.Caption = Me.ScrollBar1.Value
End Sub
Private Sub ScrollBar1_Scroll()
'This event triggers when the user drags 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 btnClose_Click()
Worksheets("Scrollbar").Range("A1").Value = Me.ScrollBar1.Value
Unload Me
End SubTable 22-5 lists the events for ScrollBar controls.
TABLE 22-5 ScrollBar control events
Event | Description |
|---|---|
| Occurs after a person has changed the control’s data. |
| Occurs while someone drags and drops data onto the control. |
| Occurs right before the person 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 right before the control receives the focus from another control on the same userform. |
| Occurs when the control runs into an error and cannot return the error information. |
| Occurs right after the control loses focus to another control on the same userform. |
| Occurs when the person presses a key on the keyboard. |
| Occurs when the person presses an ANSI key. An ANSI key is a typeable character, such as the letter A. |
| Occurs when the person releases a key on the keyboard. |
| Occurs when the slider is moved. |
Controls and collections
In Chapter 9, “Creating classes and collections,” several labels on a sheet were grouped into a collection. With a little more code, these labels were turned into help screens. Userform controls can also be grouped into collections to take advantage of class modules. The following example selects or clears all the check boxes on the userform, depending on which label someone chooses.
Place the following code in the class module, clsFormCtl. It consists of one property, chb, and two methods, SelectAll and UnselectAll.
The SelectAll method selects a check box by setting its value to True:
Public WithEvents chb As MSForms.CheckBox Public Sub SelectAll() chb.Value = True End Sub
The UnselectAll method clears 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 the frame frm_Selection, which makes it easier to create the collection because it narrows the number of controls that need to be checked from the entire userform to just those controls within the frame:
Dim col_Selection As New Collection
Private Sub UserForm_Initialize()
Dim ctl As MSForms.CheckBox
Dim chb_ctl As clsFormCtl
'Go through the members of the frame and add them to the collection
For Each ctl In frm_Selection.Controls
Set chb_ctl = New clsFormCtl
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 clear the check boxes:
Private Sub lbl_SelectAll_Click()
Dim ctl As clsFormCtl
For Each ctl In col_Selection
ctl.SelectAll
Next ctl
End SubThe following code clears the check boxes in the collection:
Private Sub lbl_unSelectAll_Click()
Dim ctl As clsFormCtl
For Each ctl In col_Selection
ctl.Unselectall
Next ctl
End SubAll the check boxes can be selected and cleared with a single click of the mouse, as shown in Figure 22-6.
FIGURE 22-6 Use frames, collections, and class modules together to create quick and efficient userforms.
Tip
If your controls cannot be placed in a frame, you can use the Tag property to create an improvised grouping. 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, you can use it to create an informal group of controls from different groupings.
Modeless userforms
Have you ever had a userform active but needed to manipulate something on the active sheet or switch to another sheet? Forms can be modeless, in which case they don’t have to interfere with the functionality of Excel. A person can type in a cell, switch to another sheet, copy/paste data, and use the ribbon—as if the userform were not there.
By default, a userform is modal, which means that there can be no interaction with Excel other than with the form. To make the form modeless, change the ShowModal property to False. For example, to make Userform1 modeless when it’s opened, do this:
Userform1.Show False
After it is modeless, someone can select a cell on the sheet while the form is active, as shown in Figure 22-7.
FIGURE 22-7 A modeless form enables a person to enter a cell while the form is still active.
Using hyperlinks in userforms
In the userform example shown in Figure 22-3, there is a field for email and a field for website address. It would be nice to click these and have a blank email message or web page appear automatically. You can do this by using the following program, which creates a new message or opens a web browser when someone clicks the corresponding label:
Private Declare PtrSafe 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 LongPtr Const SWNormal = 1
The application programming interface (API) declaration and any other constants go at the very top of the module.
This sub controls what happens when the email label is clicked, as shown in Figure 22-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
FIGURE 22-8 Turn email addresses and websites into clickable links by using a few lines of code.
This sub controls what happens when someone clicks a website label:
Private Sub lbl_Website_Click() Dim lngRow As Long lngRow = TabStrip1.Value + 1 ShellExecute 0&, "open", Cells(lngRow, 6).Value, vbNullString, _ vbNullString, SWNormal End Sub
Adding controls at runtime
It’s possible to add controls to a userform at runtime. This is convenient if you’re not sure how many items you’ll be adding to a form.
Figure 22-9 shows a plain form with 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. She can select any number of SKUs from an Excel worksheet and press a hotkey to display the form. If she selects six items on the worksheet, the form displays with a small version of each picture, as shown in Figure 22-10.
If the sales rep selects fewer items, the images are displayed larger, as shown in Figure 22-11.
A number of techniques are used to create this userform on the fly. The initial form contains only one button, cbClose. Everything else is added on the fly.
FIGURE 22-9 You can create flexible forms if you add most controls at runtime.
FIGURE 22-10 The sales rep asked to see photos of six SKUs. The UserForm_Initialize procedure adds each picture and label on the fly.
FIGURE 22-11 The logic in UserForm_Initialize decides how many pictures are being displayed and adds the appropriately sized image controls.
Resizing the userform on the fly
Giving the best view of the images in the product catalog involves 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)
Adding a control on the fly
For a normal control added at design time, such as a button called cbClose, it is easy to refer to the control by using its name:
Me.cbClose.Left = 100
However, for a control that’s added at runtime, you have to use the Controls collection to set any properties for the control. For this reason, it’s important to set up a variable, such as LC, to hold the name of the control. Controls are added with the .Add method. The important parameter is bstrProgId. This property dictates whether the added control is a label, a text box, a 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 unique name. After the form is added, specify a position for the control by setting the Top and Left properties. You should also set Height and Width properties 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 does not 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.
Sizing on the fly
In reality, you need to be able to calculate values for Top, Left, Height, and Width on the fly. You do this based on the actual height and width of a form and based on how many controls are needed.
Adding other controls
To add other types of controls, change the ProgId used with the Add method. Table 22-6 shows the ProgIds for various types of controls.
TABLE 22-6 Userform controls and corresponding ProgIds
Control | ProgId |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Adding an image on the fly
There is some unpredictability in adding images to a userform. Any given image might be shaped either landscape or portrait. An image might be small or huge. The strategy you might want to use is to let an image load at full size by setting the .AutoSize parameter to True before loading it:
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
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
'CellWid and CellHt are calculated in the full code sample below
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 so that it draws without distortion, 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
Putting it all together
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 height and width of each square
'Each column will have 2 points 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 is 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 'redraws the form
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 bottommost & rightmost 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 = 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 SubAdding help to a userform
You have already designed a great userform in this chapter, but there is one thing missing: guidance for users. The following sections show four ways you can help people fill out the form properly.
Showing accelerator keys
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. Pressing Alt + the accelerator key selects the control. For example, in Figure 22-12, Alt+T selects the Streaming check box. Repeating the combination clears the box.
FIGURE 22-12 Use accelerator key combinations, like Alt+T to select Streaming, to give userforms the power of keyboard shortcuts.
Adding control tip text
When a cursor passes over a ribbon control, 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 22-13, tip text has been added to the frame surrounding the various categories.
FIGURE 22-13 Add tips to controls to provide help to people.
Creating the tab order
People can tab from one field to another. This is an automatic feature in a form. To control which field the next tab goes to, set the TapStop property value for each control.
The first tab stop is 0, and the last tab stop is equal to the number of controls in a group. Remember that you can create a group with a frame. Excel doesn’t allow multiple controls within a group to have the same tab stop. After tab stops are set, a person can use the Tab key and spacebar to select or deselect various options.
Tip
If you right-click a userform (not one of its controls) and select Tab Order, a form appears, listing all the controls. You can reorder the controls on this form to set the tab order.
Coloring the active control
Another method for helping a person 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. RaiseEvent is used to call the events declared at the top of the class module. The code for the events is part of the userform.
Place the following code 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 code 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 SubThis 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
Creating transparent forms
Have you 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% transparency (see Figure 22-16) so that you can see the data behind it without moving the form somewhere else on the screen (and blocking more data).
FIGURE 22-16 Create a 50% transparent form to view the data on the sheet behind it.
Place the following code in the declarations section at the top of the userform:
Private Declare PtrSafe Function GetActiveWindow Lib "USER32" () As LongPtr Private Declare PtrSafe Function SetWindowLongPtr Lib "USER32" Alias _ "SetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, _ ByVal dwNewLong As LongPtr) As LongPtr Private Declare PtrSafe Function GetWindowLongPtr Lib "USER32" Alias _ "GetWindowLongA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As Long Private Declare PtrSafe Function SetLayeredWindowAttributes Lib "USER32" _ (ByVal hWnd As LongPtr, ByVal crKey As Integer, _ ByVal bAlpha As Integer, ByVal dwFlags As LongPtr) As LongPtr Private Const WS_EX_LAYERED = &H80000 Private Const LWA_COLORKEY = &H1 Private Const LWA_ALPHA = &H2 Private Const GWL_EXSTYLE = &HFFEC Dim hWnd As Long
Place the following code behind a toggle button. When the button is pressed in, the transparency is reduced 50%. When a person toggles the button back up, the transparency is set to 0:
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
'127 sets the 50% semitransparent
SetTransparency 127
Else
'a value of 255 is opaque and 0 is transparent
SetTransparency 255
End If
End Sub
Private Sub SetTransparency(TRate As Integer)
Dim nIndex As Long
hWnd = GetActiveWindow
nIndex = GetWindowLong(hWnd, GWL_EXSTYLE)
SetWindowLong hWnd, GWL_EXSTYLE, nIndex Or WS_EX_LAYERED
SetLayeredWindowAttributes hWnd, 0, TRate, LWA_ALPHA
End SubNext steps
This chapter showed you how to use more advanced userform controls. It also reviewed various methods to maximize the use of userforms. In Chapter 23, “The Windows Application Programming Interface (API),” you’ll discover more about how to access these functions and procedures that are hidden in files on your computer.