In This Chapter

• Using modeless UserForms
• Displaying a progress indicator
• Creating a wizard — an interactive series of dialog boxes
• Creating a function that emulates VBA’s MsgBox function
• Allowing users to move UserForm controls
• Displaying a UserForm with no title bar
• Simulating a toolbar with a UserForm
• Emulating a task pane with a UserForm
• Allowing users to resize a UserForm
• Handling multiple controls with a single event handler
• Using a dialog box to select a color
• Displaying a chart in a UserForm
• Creating puzzles and games with Userforms

A Modeless Dialog Box

Most dialog boxes that you encounter are modal dialog boxes, which you must dismiss from the screen before the user can do anything with the underlying application. Some dialog boxes, however, are modeless, which means the user can continue to work in the application while the dialog box is displayed.

To display a modeless UserForm, use a statement such as:

UserForm1.Show vbModeless

The keyword vbModeless is a built-in constant that has a value of 0. Therefore, the following statement works identically:

UserForm1.Show 0

Figure 15.1 shows a modeless dialog box that displays information about the active cell. When the dialog box is displayed, the user is free to move the cell cursor, activate other sheets, and perform other Excel actions. The information displayed in the dialog box changes when the active cell changes.

The key to making this UserForm work is determining when to update the information in the dialog box. To do so, the code in the example monitors two workbook events: SheetSelectionChange and SheetActivate. These event-handler procedures are located in the code module for the ThisWorkbook object.

The event-handler procedures are simple:

Private Sub Workbook_SheetSelectionChange _
  (ByVal Sh As Object, ByVal Target As Range)
    UpdateBox
End Sub
 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    UpdateBox
End Sub

The two previous procedures call the UpdateBox procedure, which follows:

Sub UpdateBox()
    With UserForm1
'       Make sure a worksheet is active
        If TypeName(ActiveSheet) <>"Worksheet" Then
            .lblFormula.Caption ="N/A"
            .lblNumFormat.Caption ="N/A"
            .lblLocked.Caption ="N/A"
        Else
            .Caption ="Cell:" & _
                ActiveCell.Address(False, False)
    '       Formula
            If ActiveCell.HasFormula Then
                .lblFormula.Caption = ActiveCell.Formula
            Else
                .lblFormula.Caption ="(none)"
            End If
    '       Number format
            .lblNumFormat.Caption = ActiveCell.NumberFormat
    '       Locked
            .lblLocked.Caption = ActiveCell.Locked
        End If
    End With
End Sub

The UpdateBox procedure changes the UserForm’s caption to show the active cell’s address; then it updates the three Label controls (lblFormula, lblNumFormat, and lblLocked).

Following are a few points to help you understand how this example works:

• The UserForm is displayed modeless so that you can still access the worksheet while it’s displayed.
• Code at the top of the procedure checks to make sure that the active sheet is a worksheet. If the sheet isn’t a worksheet, the Label controls are assigned the text N/A.
• The workbook monitors the active cell by using the SheetSelectionChange event (which is located in the ThisWorkbook code module).
• The information is displayed in Label controls on the UserForm.

Figure 15.2 shows a more sophisticated version of this example. This version displays quite a bit of additional information about the selected cell. The code is too lengthy to display here, but you can view the well-commented code in the example workbook.

Following are some key points about this more sophisticated version:

• The UserForm has a check box (Auto Update). When this check box is selected, the UserForm is updated automatically. When Auto Update isn’t turned on, the user can use the Update button to refresh the information.
• The workbook uses a class module to monitor two events for all open workbooks: the SheetSelectionChange event and the SheetActivate event. As a result, the code to display the information about the current cell is executed automatically whenever these events occur in any workbook (assuming that the Auto Update option is in effect). Some actions (such as changing a cell’s number format) do not trigger either of these events. Therefore, the UserForm also contains an Update button.

• The counts displayed for the cell precedents and dependents fields include cells in the active sheet only, due to a limitation of the Precedents and Dependents properties.
• Because the length of the information will vary, VBA code is used to size and vertically space the labels — and also change the height of the UserForm if necessary.

Displaying a Progress Indicator

One of the most common requests among Excel developers involves progress indicators. A progress indicator is a graphical thermometer-type display that shows the progress of a task, such as a lengthy macro.

In this section, I describe how to create three types of progress indicators:

• A progress bar on a UserForm that’s called by a separate macro (a stand-alone progress indicator).
• A progress bar that’s integrated into the same UserForm that initiates the macro.
• A progress bar on a UserForm that shows tasks being completed, rather than a graphical bar.

Using a progress indicator requires that your code gauge how far along your macro is in completing its given task. How you do this will vary, depending on the macro. For example, if your macro writes data to cells and you know the number of cells that will be written to, it’s a simple matter to write code that calculates the percent completed. Even if you can’t accurately gauge the progress of a macro, it’s a good idea to give the user some indication that the macro is still running and Excel hasn’t crashed.

Creating a stand-alone progress indicator

This section describes how to set up a stand-alone progress indicator — that is, one that isn’t initiated by displaying a UserForm — to display the progress of a macro. The macro in this example clears the worksheet and writes 20,000 random numbers to a range of cells:

Sub GenerateRandomNumbers()
'   Inserts random numbers on the active worksheet
    Const RowMax As Long = 500
    Const ColMax As Long = 40
    Dim r As Long, c As Long
    If TypeName(ActiveSheet) <>"Worksheet" Then Exit Sub
    Cells.Clear
    For r = 1 To RowMax
        For c = 1 To ColMax
            Cells(r, c) = Int(Rnd * 1000)
        Next c
    Next r
End Sub

After you make a few modifications to this macro (described in the next section), the UserForm, shown in Figure 15.3, displays the progress.

Building the stand-alone progress indicator UserForm

Follow these steps to create the UserForm that will be used to display the progress of your task:

1. Insert a new UserForm, change its Name property to UProgress, and change its Caption property to Progress.
2. Add a Frame control and name it frmProgress.

3. Add a Label control inside the Frame, name it lblProgress, remove the label’s caption, and make its background color (BackColor property) something that will stand out.

   The label’s size doesn’t matter for now.

4. Optional. Add another label above the frame to describe what’s going on.
5. Adjust the UserForm and controls so that they look something like Figure 15.4.

You can, of course, apply any other type of formatting to the controls. For example, I changed the SpecialEffect property for the Frame control to make it appear sunken.

Creating the code that increments the progress bar

When the form is first called, its Initialize event is triggered. The following event procedure sets the color of the progress bar to red and sets its initial width to 0.

Private Sub UserForm_Initialize()
    With Me
        .lblProgress.BackColor = vbRed
        .lblProgress.Width = 0
    End With
End Sub

The form’s SetDescription method is used to add some text above the progress bar to let the user know what’s going on. If you chose not to include this label on your form, you don’t need to include this procedure.

Public Sub SetDescription(Description As String)
    Me.lblDescription.Caption = Description
End Sub

The form’s UpdateProgress method sets the Frame’s caption and increases the width of the progress label. As the calling procedure progresses, higher percentages are passed into UpdateProgress and the label widens. Note that the UpdateProgress method uses the Repaint method of the UserForm object. Without this statement, the changes to the label would not be updated.

Public Sub UpdateProgress(PctDone As Double)
    With Me
        .frmProgress.Caption = Format(PctDone,"0%")
        .lblProgress.Width = PctDone * (.frmProgress.Width - 10)
        .Repaint
    End With
End Sub

Calling the stand-alone progress indicator from your code

The modified version of the GenerateRandomNumbers procedure (which was presented earlier) follows. Note that additional code shows the form and updates its controls to indicate progress.

Sub GenerateRandomNumbers()
'   Inserts random numbers on the active worksheet
    Dim Counter As Long
    Dim r As Long, c As Long
    Dim PctDone As Double
    Const RowMax As Long = 500
    Const ColMax As Long = 40
 
    If TypeName(ActiveSheet) <>"Worksheet" Then Exit Sub
    ActiveSheet.Cells.Clear
    UProgress.SetDescription"Generating random numbers..."
    UProgress.Show vbModeless
    Counter = 1
    For r = 1 To RowMax
        For c = 1 To ColMax
            ActiveSheet.Cells(r, c) = Int(Rnd * 1000)
            Counter = Counter + 1
        Next c
        PctDone = Counter / (RowMax * ColMax)
        UProgress.UpdateProgress PctDone
    Next r
    Unload UProgress
End Sub

The GenerateRandomNumbers procedure calls the form’s SetDescription property and shows the form modeless so the remaining code continues to run. The procedure then executes two loops to write random values to cells, keeping count as it goes. In the outer loop the procedure calls the form’s UpdateProgress method, which takes one argument (the PctDone variable, which represents the progress of the macro). PctDone will contain a value between 0 and 1. At the end of the procedure, the form is unloaded.

Benefits of a stand-alone progress indicator

You now have a UserForm that you can call from any procedure where you want to show progress. Simply show the form modeless and call the UpdateProgress method in the appropriate place in your code. There’s nothing in this UserForm that ties it to a particular calling procedure. The only requirement is that you send it increasing percentages, and the form takes care of the rest.

In the calling procedure, you need to figure out how to determine the percentage completed and assign it to the PctDone variable. In this example, you know how many cells you are going to fill and you only need to keep a count of how many have already been filled to calculate the progress. This calculation will be different for other calling procedures. If your code runs in a loop (as in this example), determining the percentage completed is easy. If your code is not in a loop, you might need to estimate the progress completed at various points in your code.

Showing a progress indicator that’s integrated into a UserForm

In the preceding example, you called a progress indicator UserForm that was completely separate from your calling procedure. You may prefer to include the progress indicator directly on the UserForm that’s running the code. In this section, I’ll show you a couple of options for including a professional looking progress indicator right on your form.

Like the previous example, this one enters random numbers into a worksheet. The difference here is that the application contains a UserForm that allows the user to specify the number of rows and columns for the random numbers (see Figure 15.5).

Modifying your UserForm for a progress indicator with a MultiPage control

The first technique will display a progress indicator on another page of a MultiPage control. This step assumes that you have a UserForm all set up. You’ll add a MultiPage control. The first page of the MultiPage control will contain all your original UserForm controls. The second page will contain the controls that display the progress indicator. When the macro begins executing, VBA code will change the Value property of the MultiPage control to effectively hide the original controls and display the progress indicator.

The first step is to add a MultiPage control to your UserForm. Then move all the existing controls on the UserForm to Page1 of the MultiPage control.

Next, activate Page2 of the MultiPage control and set it up as shown in Figure 15.6. This is essentially the same combination of controls used in the example in the previous section.

Follow these steps to set up the MultiPage control:

1. Add a Frame control and name it frmProgress.
2. Add a Label control inside the Frame, name it lblProgress, remove the label’s caption, and make its background color red.
3. Optional. Add another label to describe what’s going on.
4. Next, activate the MultiPage control itself (not a page on the control) and set its Style property to 2 – fmTabStyleNone. (This will hide the tabs.) You’ll probably need to adjust the size of the MultiPage control to account for the fact that the tabs aren’t displayed.

Inserting the UpdateProgress procedure for a progress indicator with a MultiPage control

Insert the following procedure in the code module for the UserForm:

Sub UpdateProgress(Pct)
    With Me
        .frmProgress.Caption = Format(Pct,"0%")
        .frmProgress.Width = Pct * (.frmProgress.Width - 10)
        .Repaint
    End With
End Sub

The UpdateProgress procedure is called from the macro that’s executed when the user clicks the OK button and performs the updating of the progress indicator.

Modifying your procedure for a progress indicator with a MultiPage control

You need to modify the procedure that is executed when the user clicks the OK button — the Click event-handler procedure for the button named cmdOK_Click. First, insert the following statement at the top of your procedure:

Me.mpProgress.Value = 1

This statement activates Page2 of the MultiPage control (the page that displays the progress indicator). If you named your MultiPage control something other than mpProgress, you’ll need to adjust the code to use your name.

In the next step, you’re pretty much on your own. You need to write code to calculate the percent completed and assign this value to a variable named PctDone. Most likely, this calculation will be performed inside a loop. Then insert the following statement, which will update the progress indicator:

UpdateProgress(PctDone)

How a progress indicator with a MultiPage control works

Using a MultiPage control as a progress indicator is straightforward and, as you’ve seen, it involves only one UserForm. The code switches pages of the MultiPage control and converts your normal dialog box into a progress indicator. Because the MultiPage tabs are hidden, it doesn’t even resemble a MultiPage control.

Showing a progress indicator without using a MultiPage control

The second technique is simpler because it doesn’t use a MultiPage control. Rather, the progress indicator is stored at the bottom of the UserForm — but the UserForm’s height is reduced so that the progress indicator controls aren’t visible. When it’s time to display the progress indicator, the UserForm’s height is increased, which makes the progress indicator visible.

Figure 15.7 shows the UserForm in VBE.

The Height property of the UserForm is 177. However, before the UserForm is displayed, the VBA code changes the Height to 130 (which means the progress indicator controls aren’t visible to the user). When the user clicks OK, the VBA code changes the Height property to 177 with the following statement:

Me.Height = 177

Figure 15.8 shows the UserForm with the progress indicator section unhidden.

Creating a non-graphical progress indicator

The preceding examples showed graphical progress indicators by increasing the width of a label. If you have a smaller number of steps, you may prefer to show a description of the steps as they’re completed. The following procedure processes a small number of text files in a folder. Instead of showing a progress bar, you can list the names of the files as they are processed.

Sub ProcessFiles()
 
    Dim sFile As String, lFile As Long
    Const sPATH As String ="C:Text Files"
 
    sFile = Dir(sPATH &"*.txt")
    Do While Len(sFile) > 0
        ImportFile sFile
        sFile = Dir
    Loop
 
End Sub

The procedure finds all the text files in a directory and calls another procedure that imports them. What happens to the file isn’t important, merely that there are a finite number of steps to be completed.

Creating the UserForm to display the steps

Figure 15.9 shows the simple UserForm in the VBE. It only has two controls: a label to describe what’s happening and a ListBox control to list the steps.

The code behind the UserForm is equally simple. You change the descriptive label by calling the SetDescription procedure. As the calling procedure progresses, you call the AddStep procedure to add an item to the ListBox. The TopIndex property of the ListBox object keeps the most recent steps visible if the ListBox isn’t tall enough.

Public Sub AddStep(sStep As String)
    With Me.lbxSteps
        .AddItem sStep
        .TopIndex = Application.Max(.ListCount, .ListCount - 6)
    End With
    Me.Repaint
End Sub

Modifying the calling procedure to use the progress indicator

The ProcessFiles procedure shown below has been modified to use the progress indicator as it processes files. First, the UserForm’s Caption property is set to indicate what process is occurring. Next, the SetDescription method is called so the user knows what’s appearing in the ListBox control. The Show method includes the vbModeless parameter that allows the calling procedure to continue executing. Inside the loop, the AddStep method adds file names to indicate progress. Figure 15.10 shows the userform in action.

Sub ProcessFiles()
    Dim sFile As String, lFile As Long
    Const sPATH As String ="C:Text Files"
 
    sFile = Dir(sPATH &"*.txt")
    UProgress.Caption ="Proccesing File Progress"
    UProgress.SetDescription"Completed files..."
    UProgress.Show vbModeless
 
    Do While Len(sFile) > 0
        ImportFile sFile
        UProgress.AddStep sPATH & sFile
        sFile = Dir
    Loop
    Unload UProgress
End Sub

This progress indicator is similar to the stand-alone progress indicator shown in a previous section. It doesn’t care what the steps to your procedure are. You can process files, fill ranges on a Worksheet, or perform any number of steps. By changing the Caption property and calling the SetDescription method, you can customize this progress indicator for whatever process you’re completing.

Creating Wizards

Many applications incorporate wizards to guide users through an operation. Excel’s Text Import Wizard is a good example. A wizard is essentially a series of dialog boxes that solicit information from the user. Usually, the user’s choices in earlier dialog boxes influence the contents of later dialog boxes. In most wizards, the user is free to go forward or backward through the dialog box sequence or to click the Finish button to accept all defaults.

You can create wizards by using VBA and a series of UserForms. However, I’ve found that the most efficient way to create a wizard is to use a single UserForm and a MultiPage control with the tabs hidden.

Figure 15.11 shows an example of a simple four-step wizard, which consists of a single UserForm that contains a MultiPage control. Each step of the wizard displays a different page in the MultiPage control.

The sections that follow describe how I created the sample wizard.

Programming the wizard’s buttons

Each of the four wizard buttons requires a procedure to handle its Click event. The event handler for the cmdCancel control follows.

Private Sub cmdCancel_Click()
    Dim Msg As String
    Dim Ans As Long
    Msg ="Cancel the wizard?"
    Ans = MsgBox(Msg, vbQuestion + vbYesNo, APPNAME)
    If Ans = vbYes Then Unload Me
End Sub

This procedure uses a MsgBox function (see Figure 15.12) to verify that the user really wants to exit. If the user clicks the Yes button, the UserForm is unloaded with no action taken. This type of verification, of course, is optional.

The event-handler procedures for the Back and Next buttons follow:

Private Sub cmdBack_Click()
    Me.mpgWizard.Value = Me.mpgWizard.Value - 1
    UpdateControls
End Sub
 
Private Sub cmdNext_Click()
    Me.mpgWizard.Value = Me.mpgWizard.Value + 1
    UpdateControls
End Sub

These two procedures are simple. They change the Value property of the MultiPage control and then call another procedure named UpdateControls (which follows).

The UpdateControls procedure is responsible for enabling and disabling the cmdBack and cmdNext controls.

Sub UpdateControls()
'   Enable back if not on page 1
    Me.cmdBack.Enabled = Me.mpgWizard.Value > 0
'   Enable next if not on the last page
    Me.cmdNext.Enabled = Me.mpgWizard.Value < Me.mpgWizard.Pages.Count - 1
 
'   Update the caption
    Me.Caption = APPNAME &" Step" _
      & Me.mpgWizard.Value + 1 &" of" _
      & Me.mpgWizard.Pages.Count
 
'   the Name field is required
    Me.cmdFinish.Enabled = Len(Me.tbxName.Text) > 0
End Sub

The procedure checks the Value property of the MultiPage control to see what page is showing. If the first page is showing, the Enabled property of cmdBack is set to False. If the last page is showing, the Enabled property of cmdNext is set to False. Next, the procedure changes the UserForm’s caption to display the current step and the total number of steps. APPNAME is a public constant, defined in Module1. The procedure then examines the name field on the first page (a TextBox named tbxName). This field is required, so if it’s empty the user can’t click the Finish button. If the TextBox is empty (the length of its contents is zero), cmdFinish is disabled; otherwise, it’s enabled.

Private Sub mpgWizard_Change()
    Dim TopPos As Long
    Dim FSpace As Long
    Dim AtLeastOne As Boolean
    Dim i As Long
 
'   Set up the Ratings page?
    If Me.mpgWizard.Value = 3 Then
'       Create an array of CheckBox controls
        Dim ProdCB(1 To 3) As MSForms.CheckBox
        Set ProdCB(1) = Me.chkExcel
        Set ProdCB(2) = Me.chkWord
        Set ProdCB(3) = Me.chkAccess
 
'       Create an array of Frame controls
        Dim ProdFrame(1 To 3) As MSForms.Frame
        Set ProdFrame(1) = Me.frmExcel
        Set ProdFrame(2) = Me.frmWord
        Set ProdFrame(3) = Me.frmAccess
 
        TopPos = 22
        FSpace = 8
        AtLeastOne = False
 
'       Loop through all products
        For i = 1 To 3
            If ProdCB(i).Value Then
                ProdFrame(i).Visible = True
                ProdFrame(i).Top = TopPos
                TopPos = TopPos + ProdFrame(i).Height + FSpace
                AtLeastOne = True
            Else
                ProdFrame(i).Visible = False
            End If
        Next i
 
'       Uses no products?
        If AtLeastOne Then
            Me.lblHeadings.Visible = True
            Me.imgRating.Visible = True
            Me.lblFinishMsg.Visible = False
        Else
            Me.lblHeadings.Visible = False
            Me.imgRating.Visible = False
            Me.lblFinishMsg.Visible = True
            If Len(Me.tbxName.Text) = 0 Then
                Me.lblFinishMsg.Caption = _
                  "A name is required in Step 1."
            Else
                Me.lblFinishMsg.Caption = _
                  "Click Finish to exit."
             End If
        End If
    End If
End Sub

Private Sub cmdFinish_Click()
    Dim r As Long
 
    r = Application.WorksheetFunction. _
      CountA(Range("A:A")) + 1
 
'   Insert the name
    Cells(r, 1) = Me.tbxName.Text
 
'   Insert the gender
    Select Case True
        Case Me.optMale.Value: Cells(r, 2) ="Male"
        Case Me.optFemale: Cells(r, 2) ="Female"
        Case Me.optNoAnswer: Cells(r, 2) ="Unknown"
    End Select
 
'   Insert usage
    Cells(r, 3) = Me.chkExcel.Value
    Cells(r, 4) = Me.chkWord.Value
    Cells(r, 5) = Me.chkAccess.Value
 
'   Insert ratings
    If Me.optExcelNo.Value Then Cells(r, 6) =""
    If Me.optExcelPoor.Value Then Cells(r, 6) = 0
    If Me.optExcelGood.Value Then Cells(r, 6) = 1
    If Me.optExcelExc.Value Then Cells(r, 6) = 2
    If Me.optWordNo.Value Then Cells(r, 7) =""
    If Me.optWordPoor.Value Then Cells(r, 7) = 0
    If Me.optWordGood.Value Then Cells(r, 7) = 1
    If Me.optWordExc.Value Then Cells(r, 7) = 2
    If Me.optAccessNo.Value Then Cells(r, 8) =""
    If Me.optAccessPoor.Value Then Cells(r, 8) = 0
    If Me.optAccessGood.Value Then Cells(r, 8) = 1
    If Me.optAccessExc.Value Then Cells(r, 8) = 2
 
    Unload Me
End Sub

Emulating the MsgBox Function

The VBA MsgBox function (discussed in Chapter 12) is a bit unusual because, unlike most functions, it displays a dialog box. But, similar to other functions, it also returns a value: an integer that represents which button the user clicked.

This section describes a custom function that I created that emulates the VBA MsgBox function. On first thought, creating such a function might seem easy. Think again! The MsgBox function is extraordinarily versatile because of the arguments that it accepts. Consequently, creating a function to emulate MsgBox is no small feat.

I named my pseudo-MsgBox function MyMsgBox. The emulation is close but not perfect. The MyMsgBox function has the following limitations:

• It does not support the Helpfile argument (which adds a Help button that, when clicked, opens a Help file).
• It does not support the Context argument (which specifies the context ID for the Help file).
• It does not support the system modal option, which puts everything in Windows on hold until you respond to the dialog box.
• It does not play a sound when it is called.

The syntax for MyMsgBox is:

MyMsgBox(prompt[, buttons] [, title])

This syntax is the same as the MsgBox syntax except it doesn’t use the last two optional arguments (Helpfile and Context). MyMsgBox also uses the same predefined constants as MsgBox: vbOKOnly, vbQuestion, vbDefaultButton1, and so on.

MsgBox emulation: MyMsgBox code

The MyMsgBox function uses a UserForm named UMsgBox. The function itself, which follows, sets up the UserForm according to the arguments passed to the function. It calls several other procedures to make many of the settings.

Function MyMsgBox(ByVal Prompt As String, _
    Optional ByVal Buttons As Long, _
    Optional ByVal Title As String) As Long
'   Emulates VBA's MsgBox function
'   Does not support the HelpFile or Context arguments
    With UMsgBox
    '   Do the Caption
        If Len(Title) > 0 Then .Caption = Title _
            Else .Caption = Application.Name
        SetImage Buttons
        SetPrompt Prompt
        SetButtons Buttons
        .Height = .cmdLeft.Top + 54
        SetDefaultButton Buttons
        .Show
    End With
    MyMsgBox = UMsgBox.UserClick
End Function

Figure 15.13 shows MyMsgBox in use. It looks similar to the VBA message box, but I used a different font for the message text and also used some different icons.

If you use a multiple monitor system, the position of the displayed UserForm may not be centered in Excel’s window. To solve that problem, use the following code to display UMsgBox:

With UMsgBox
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
End With

Here’s the code that I used to execute the function:

Prompt ="You have chosen to save this workbook" & vbCrLf
Prompt = Prompt &"on a drive that is not available to"  & vbCrLf
Prompt = Prompt &"all employees." & vbCrLf & vbCrLf
Prompt = Prompt &"OK to continue?"
Buttons = vbQuestion + vbYesNo
Title ="Network Location Notice"
Ans = MyMsgBox(Prompt, Buttons, Title)

How the MyMsgBox function works

The MyMsgBox function examines the arguments and it, or one of the procedures it calls, does the following:

• Determines which, if any, image to display (and hides the others)
• Determines which button(s) to display (and hides the others)
• Determines which button is the default button
• Centers the buttons in the dialog box
• Determines the captions for the CommandButtons
• Determines the position of the text within the dialog box
• Determines the width and height of the dialog box (by using an API function call to get the video resolution)
• Displays the UserForm

Interpreting the second argument (buttons) is challenging. This argument can consist of a number of constants added together. For example, the second argument can be something like this:

VbYesNoCancel + VbQuestion + VbDefaultButton3

This argument creates a three-button MsgBox (with Yes, No, and Cancel buttons), displays the question mark icon, and makes the third button the default button. The actual argument is 547 (3 + 32 + 512).

To determine what to display on the UserForm, the function uses a technique called Bitwise And. Each of the three arguments can be one of a series of numbers and those numbers don’t overlap with the other arguments. The six types of buttons that you can display are numbered 0 to 5. If you add up all of the numbers 0 to 5, you get 15. The lowest value of the possible icon values is 16, which is one more than all of the buttons put together.

One of the procedures called by MyMsgBox is named SetDefaultButtons and is shown below. It uses Bitwise And by comparing the Buttons argument to a constant, such as vbDefaultButton3. If the result of the Bitwise And is equal to vbDefaultButton3, then you can be sure that vbDefaultButton3 was one of the choices that made up the Buttons argument, regardless of any other choices included in that argument.

Private Sub SetDefaultButton(Buttons As Long)
    With UMsgBox
        Select Case True
           Case (Buttons And vbDefaultButton4) = vbDefaultButton4
               .cmdLeft.Default = True
               .cmdLeft.TabIndex = 0
           Case (Buttons And vbDefaultButton3) = vbDefaultButton3
               .cmdRight.Default = True
               .cmdRight.TabIndex = 0
           Case (Buttons And vbDefaultButton2) = vbDefaultButton2
               .cmdMiddle.Default = True
               .cmdMiddle.TabIndex = 0
           Case Else
               .cmdLeft.Default = True
               .cmdLeft.TabIndex = 0
        End Select
    End With
End Sub

The UserForm (shown in Figure 15.14) contains four Label controls. Each of these Label controls has an image, which I pasted into the Picture property. The UserForm also has three CommandButton controls and a TextBox control.

Three additional event-handler procedures are included (one for each CommandButton). These routines determine which button was clicked and return a value for the function by setting a value for the UserClick variable.

A UserForm with Movable Controls

The UserForm shown in Figure 15.15 contains three Image controls. The user can use the mouse to drag these images around in the dialog box. I’m not sure of the practical significance of this technique, but the example in this section will help you understand mouse-related events.

Each Image control has two associated event procedures: MouseDown and MouseMove. The event procedures for the Image1 control are shown here. (The others are identical except for the control names.)

Private Sub Image1_MouseDown(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'   Starting position when button is pressed
    OldX = X
    OldY = Y
    Image1.ZOrder 0
End Sub
 
Private Sub Image1_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'   Move the image
    If Button = 1 Then
        Image1.Left = Image1.Left + (X - OldX)
        Image1.Top = Image1.Top + (Y - OldY)
    End If
End Sub

When the mouse button is pressed, the MouseDown event occurs, and the X and Y positions of the mouse pointer are stored. Two public variables are used to keep track of the original position of the controls: OldX and OldY. This procedure also changes the ZOrder property, which puts the image on top of the others.

When the mouse is being moved, the MouseMove event occurs repeatedly. The event procedure checks the mouse button. If the Button argument is 1, it means that the left mouse button is depressed. If so, then the Image control is shifted relative to its old position.

Also note that the mouse pointer changes when it’s over an image. That’s because the MousePointer property is set to 15 - fmMousePointerSizeAll, a mouse pointer style that’s commonly used to indicate that an item can be dragged.

A UserForm with No Title Bar

Excel provides no direct way to display a UserForm without its title bar. But this feat is possible with the help of a few API functions. Figure 15.16 shows a UserForm with no title bar.

Another example of a UserForm without a title bar is in Figure 15.17. This dialog box contains an Image control and a CommandButton control.

Displaying a UserForm without a title bar requires four windows API functions: GetWindowLong, SetWindowLong, DrawMenuBar, and FindWindowA (see the example file for the function declarations). The UserForm_Initialize procedure calls these functions:

Private Sub UserForm_Initialize()
    Dim lngWindow As Long, lFrmHdl As Long
    lFrmHdl = FindWindowA(vbNullString, Me.Caption)
    lngWindow = GetWindowLong(lFrmHdl, GWL_STYLE)
    lngWindow = lngWindow And (Not WS_CAPTION)
    Call SetWindowLong(lFrmHdl, GWL_STYLE, lngWindow)
    Call DrawMenuBar(lFrmHdl)
End Sub

One problem is that the user has no way to reposition a dialog box without a title bar. The solution is to use the MouseDown and MouseMove events, as described in the preceding section.

Simulating a Toolbar with a UserForm

Creating a custom toolbar in versions prior to Excel 2007 was relatively easy. Beginning with Excel 2007, you can no longer create custom toolbars. More accurately, you can still create custom toolbars with VBA, but Excel ignores many of your VBA instructions. Beginning with Excel 2007, all custom toolbars are displayed in the Add-Ins ➜ Custom Toolbars Ribbon group. You can’t move, float, resize, or dock these toolbars.

This section describes how to create a toolbar alternative: a modeless UserForm that simulates a floating toolbar. Figure 15.18 shows a UserForm that may substitute for a toolbar. It uses Windows API calls to make the title bar a bit shorter than normal, and also displays the UserForm with square (rather than rounded) corners. The Close button is also smaller.

The UserForm contains eight Image controls, and each executes a macro. Figure 15.19 shows the UserForm in VBE. Note that:

• The controls aren’t aligned.
• The images displayed are not necessarily the final images.
• The UserForm isn’t the final size.
• The title bar is the standard size.

The VBA code takes care of the cosmetic details, including borrowing images from Excel’s Ribbon. For example, this statement assigns an image to the Image1 control:

Image1.Picture = Application.CommandBars. _
   GetImageMso("ReviewAcceptChange", 32, 32)

See Chapter 17 for more information about accessing images from the Ribbon.

The code also aligns the controls and adjusts the size of the UserForm to eliminate wasted space. In addition, the code uses Windows API functions to make the UserForm’s title bar smaller — just like a real toolbar. To make the UserForm look even more like a toolbar, I also set the ControlTipText property of each Image control — which displays a toolbar-like tooltip when the mouse cursor is hovered over the control.

If you open the example file, you’ll also notice that the images increase in size slightly when the mouse cursor is hovered over them. That’s because each Image control has an associated MouseMove event handler that changes the size. Here’s the MouseMove event handler procedure for Image1 (the others are identical):

Private Sub Image1_MouseMove(ByVal Button As Integer, _
   ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call NormalSize
    Image1.Width = 26
    Image1.Height = 26
End Sub

This procedure calls the NormalSize procedure, which returns each image to its normal size:

Private Sub NormalSize()
'   Make all controls normal size
    Dim ctl As Control
    For Each ctl In Controls
        ctl.Width = 24
        ctl.Height = 24
    Next ctl
End Sub

The net effect is that the user gets some visual feedback when the mouse cursor moves over a control — just like a real toolbar. The toolbar simulation only goes so far, however. You can’t resize the UserForm (for example, make the images display vertically rather than horizontally). And, of course, you can’t dock the pseudotoolbar to one of the Excel window borders.

Emulating a Task Pane with a UserForm

Beginning in Office 2013, task panes took on an expanded role and are used for adjusting formatting for many objects, including charts and images. The task panes also got a new look.

The UserForm in Figure 15.20 is an attempt to emulate the look of a built-in task pane. The example is the same as the modeless UserForm example at the beginning of the chapter (refer to Figure 15.2). You can move the UserForm by dragging its title (the same way your move a task pane). The UserForm also has an X (Close) button in the upper-right corner. And, like a task pane, it displays a vertical scrollbar only when needed.

The task pane shown in the figure has a white background. The color of the task pane background varies, depending on the Office theme (specified in the General tab of the Excel Options dialog box). I made the background of the control transparent, and used this code to set the background color:

Me.BackColor = RGB(255, 255, 255)
Frame1.BackColor = RGB(255, 255, 255)
Frame2.BackColor = RGB(255, 255, 255)

Frame controls cannot have a transparent background, so I had to set the background color of the two Frame controls separately.

To create a UserForm that has a background color that matches the Light Gray theme, use this expression:

RGB(240, 240, 240)

To emulate the Dark Gray theme, use this expression:

RGB(222, 222, 222)

The UserForm has the basic look of a task pane, but it falls short in terms of behavior. For example, the sections cannot be collapsed and it’s not possible to dock the UserForm to the side of the screen. Also, it’s not resizable by the user — but it could be (see the next section).

A Resizable UserForm

Excel uses several resizable dialog boxes. For example, you can resize the Name Manager dialog box by clicking and dragging the bottom-right corner.

If you’d like to create a resizable UserForm, you’ll eventually discover that there’s no direct way to do it. One solution is to resort to Windows API calls. That method works, but it’s complicated to set up and doesn’t generate any events, so your code can’t respond when the UserForm is resized. In this section, I present a much simpler technique for creating a user-resizable UserForm.

Figure 15.21 shows the UserForm that’s described in this section. It contains a ListBox control that displays data from a worksheet. The scrollbars on the ListBox indicate that the ListBox contains information that doesn’t fit. In addition, a (perhaps) familiar sizing control appears in the bottom-right corner of the dialog box.

Figure 15.22 shows the same UserForm after the user resized it. The size of the ListBox is also increased, and the Close button remains in the same relative position. You can stretch this UserForm to the limits of your monitor.

The sizing control at the bottom-right corner is actually a Label control that displays a single character: The letter o (character 111) from the Marlett font, character set 2. This control (named objResizer) is added to the UserForm in the UserForm_Initialize procedure at runtime:

Private Sub UserForm_Initialize()
'   Add a resizing control to bottom right corner of UserForm
    Set objResizer = Me.Controls.Add("Forms.label.1", MResizer, True)
    With objResizer
        .Caption = Chr(111)
        .Font.Name ="Marlett"
        .Font.Charset = 2
        .Font.Size = 14
        .BackStyle = fmBackStyleTransparent
        .AutoSize = True
        .ForeColor = RGB(100, 100, 100)
        .MousePointer = fmMousePointerSizeNWSE
        .ZOrder
        .Top = Me.InsideHeight - .Height
        .Left = Me.InsideWidth - .Width
    End With
End Sub

This technique relies on these facts:

• The user can move a control on a UserForm (see “A UserForm with Movable Controls,” earlier in this chapter).
• Events exist that can identify mouse movements and pointer coordinates. Specifically, these events are MouseDown and MouseMove.
• VBA code can change the size of a UserForm at runtime, but a user cannot.

Do a bit of creative thinking about these facts, and you see that it’s possible to translate the user’s movement of a Label control into information that you can use to resize a UserForm.

When the user clicks the objResizer Label object, the objResizer_MouseDown event-handler procedure is executed:

Private Sub objResizer_MouseDown(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        LeftResizePos = X
        TopResizePos = Y
    End If
End Sub

This procedure executes only if the left mouse button is pressed (that is, the Button argument is 1) and the cursor is on the objResizer label. The X and Y mouse coordinates at the time of the button click are stored in module-level variables: LeftResizePos and TopResizePos.

Subsequent mouse movements fire the MouseMove event, and the objResizer_MouseMove event handler kicks into action. Here’s an initial take on this procedure:

Private Sub objResizer_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Button = 1 Then
        With objResizer
           .Move .Left + X - LeftResizePos, .Top + Y - TopResizePos
           Me.Width = Me.Width + X - LeftResizePos
           Me.Height = Me.Height + Y - TopResizePos
           .Left = Me.InsideWidth - .Width
           .Top = Me.InsideHeight - .Height
        End With
     End If
End Sub

If you study the code, you’ll see that the UserForm’s Width and Height properties are adjusted based on the movement of the objResizer Label control. Figure 15.23 shows how the UserForm looks after the user moves the Label control down and to the right.

The problem, of course, is that the other controls in the UserForm don’t respond to the UserForm’s new size. The ListBox should be expanded, and the CommandButton should be relocated so that it remains in the lower-left corner.

More VBA code is needed to adjust the controls in the UserForm when the UserForm size is changed. The location for this new code is in the objResizer_MouseMove event-handler procedure. The statements that follow do the job:

'   Adjust the ListBox
    On Error Resume Next
    With ListBox1
        .Width = Me.Width - 37
        .Height = Me.Height - 100
    End With
    On Error GoTo 0
 
'   Adjust the Close Button
    With CloseButton
        .Left = Me.Width - 85
        .Top = Me.Height - 54
    End With

These two controls are adjusted relative to the UserForm’s size (that is, Me). After adding this new code, the dialog box works like a charm. The user can make the dialog box as large as needed, and the controls adjust.

It should be clear that the most challenging part of creating a resizable dialog box is figuring out how to adjust the controls. When you have more than two or three controls, coding can get complicated.

Handling Multiple UserForm Controls with One Event Handler

Every CommandButton on a UserForm must have its own procedure to handle its events. For example, if you have two CommandButtons, you’ll need two event-handler procedures for the controls’ click events:

Private Sub CommandButton1_Click()
' Code goes here
End Sub
 
Private Sub CommandButton2_Click()
' Code goes here
End Sub

In other words, you can’t assign a macro to execute when any CommandButton is clicked. Each Click event handler is hard-wired to its CommandButton. You can, however, have each event handler call another all-inclusive macro in the event-handler procedures, but you’ll need to pass an argument to indicate which button was clicked. In the following examples, clicking either CommandButton1 or CommandButton2 executes the ButtonClick procedure, and the single argument tells the ButtonClick procedure which button was clicked:

Private Sub CommandButton1_Click()
     Call ButtonClick(1)
End Sub
 
Private Sub CommandButton2_Click()
     Call ButtonClick(2)
End Sub

If your UserForm has many CommandButtons, setting up all these event handlers can get tedious. You might prefer to have a single procedure that can determine which button was clicked and take the appropriate action.

This section describes a way around this limitation by using a class module to define a new class.

The following steps describe how to re-create the example UserForm shown in Figure 15.24:

1. Create your UserForm as usual and add several CommandButtons. (The example contains 16 CommandButton controls.) This example assumes that the form is named UserForm1.

2. Insert a class module into your project (choose Insert ➜ Class Module), give it the name BtnClass, and enter the following code:

   
   Public WithEvents ButtonGroup As MsForms.CommandButton
    
   Private Sub ButtonGroup_Click()
       Dim Msg As String
       Msg ="You clicked" & ButtonGroup.Name & vbCrLf & vbCrLf
       Msg = Msg &"Caption:" & ButtonGroup.Caption & vbCrLf
       Msg = Msg &"Left Position:" & ButtonGroup.Left & vbCrLf
       Msg = Msg &"Top Position:" & ButtonGroup.Top
       MsgBox Msg, vbInformation, ButtonGroup.Name
   End Sub
   

   You will need to customize the ButtonGroup_Click procedure.

3. Insert a normal VBA module and enter the following code:

   Sub ShowDialog()
       UserForm1.Show
   End Sub
    
   

   This routine simply displays the UserForm.

4. In the code module for the UserForm, enter the UserForm_Initialize code that follows.

   Dim Buttons() As New BtnClass
    
   Private Sub UserForm_Initialize()
       Dim ButtonCount As Long
       Dim ctl As Control
    
   '   Create the Button objects
       ButtonCount = 0
       For Each ctl In Me.Controls
           If TypeName(ctl) ="CommandButton" Then
               'Skip the OK Button
               If ctl.Name <>"cmdOK" Then
                   ButtonCount = ButtonCount + 1
                   ReDim Preserve Buttons(1 To ButtonCount)
                   Set Buttons(ButtonCount).ButtonGroup = ctl
               End If
           End If
       Next ctl
   End Sub
   

This procedure is triggered by the UserForm’s Initialize event. Note that the code excludes a button named cmdOK from the button group. Therefore, clicking the OK button doesn’t execute the ButtonGroup_Click procedure.

After performing these steps, you can execute the ShowDialog procedure to display the UserForm. Clicking any CommandButton (except the OK button) executes the ButtonGroup_Click procedure. Figure 15.25 shows an example of the message displayed when a button is clicked.

Selecting a Color in a UserForm

The example in this section is a function that displays a dialog box (similar in concept to the MyMsgBox function, presented earlier). The function, named GetAColor, returns a color value:

Function GetAColor() As Variant
    UGetAColor.Show
    GetAColor = UGetAColor.ColorValue
    Unload UGetAColor
End Function

You can use the GetAColor function with a statement like the following:

UserColor = GetAColor()

Executing this statement displays the UserForm. The user selects a color and clicks OK. The function then assigns the user’s selected color value to the UserColor variable.

The UserForm, shown in Figure 15.26, contains three ScrollBar controls — one for each of the color components (red, green, and blue). The value range for each ScrollBar is from 0 to 255. The module contains procedures for the ScrollBar Change events. For example, here’s the procedure that’s executed when the first ScrollBar is changed:

Private Sub scbRed_Change()
    Me.lblRed.BackColor = RGB(Me.scbRed.Value, 0, 0)
    UpdateColor
End Sub

The UpdateColor procedure adjusts the color sample displayed, and also updates the RGB values.

The GetAColor UserForm has another twist: It remembers the last color that was selected. When the function ends, the three ScrollBar values are stored in the Windows Registry, using this code (APPNAME is a string defined in Module1):

SaveSetting APPNAME,"Colors","RedValue", Me.scbRed.Value
SaveSetting APPNAME,"Colors","BlueValue", Me.scbBlue.Value
SaveSetting APPNAME,"Colors","GreenValue", scbGreen.Value

The UserForm_Initialize procedure retrieves these values and assigns them to the scrollbars:

Me.scbRed.Value = GetSetting(APPNAME,"Colors","RedValue", 128)
Me.scbGreen.Value = GetSetting(APPNAME,"Colors","GreenValue", 128)
Me.scbBlue.Value = GetSetting(APPNAME,"Colors","BlueValue", 128)

The last argument for the GetSetting function is the default value, which is used if the Registry key is not found. In this case, each color defaults to 128, which produces middle gray.

The SaveSetting and GetSetting functions always use this Registry key:

HKEY_CURRENT_USERSoftwareVB and VBA Program Settings

Figure 15.27 shows the Registry data, displayed with the Windows Regedit.exe program.

Displaying a Chart in a UserForm

Excel provides no direct way to display a chart in a UserForm. You can, of course, copy the chart and paste it to the Picture property of an Image control, but this creates a static image of the chart, so it won’t display any changes that are made to the chart.

This section describes a technique to display a chart in a UserForm. Figure 15.28 shows a UserForm with a chart displayed in an Image object. The chart resides on a worksheet, and the UserForm always displays the current chart. This technique works by copying the chart to a temporary graphics file and then using the LoadPicture function to specify that file for the Image control’s Picture property.

To display a chart in a UserForm, follow these general steps:

1. Create your chart or charts as usual.
2. Insert a UserForm and then add an Image control.
3. Write VBA code to save the chart as a GIF file and then set the Image control’s Picture property to the GIF file. You need to use VBA’s LoadPicture function to do this task.
4. Add other bells and whistles as desired. For example, the UserForm in the demo file contains controls that let you change the chart type. Alternatively, you could write code to display multiple charts.

Set CurrentChart = Sheets("Data").ChartObjects(1).Chart
Fname = ThisWorkbook.Path &"	emp.gif"
CurrentChart.Export FileName:=Fname, FilterName:="GIF"

Me.Image1.Picture = LoadPicture(Fname)

Making a UserForm Semitransparent

Normally, a UserForm is opaque — it completely hides whatever is underneath it. However, you can make a UserForm semitransparent, such that the user can see the worksheet under the UserForm.

Creating a semitransparent UserForm requires a number of Windows API functions. You can set the transparency level using values that range from 0 (UserForm is invisible) to 255 (UserForm is completely opaque, as usual). Values in between 0 and 255 specify a level of semitransparency.

Figure 15.29 shows an example of a UserForm with a transparency level of about 128.

What good is a semitransparent UserForm? After giving this question some thought, I came up with a potential use for this technique: creating a light-box effect. You’ve probably seen websites that use the light-box effect. The web page is dimmed (as if the lights are lowered), and an image or a pop-up is displayed. This effect serves to focus the user’s attention to a specific item on the screen.

Figure 15.30 shows an Excel workbook that uses the light-box effect. Excel’s window is dimmed, but the message box is displayed normally. How does it work? I created a UserForm with a black background. Then I wrote code to resize and position the UserForm so that it covers Excel’s window. Here’s the code to accomplish the cover-up:

With Me
  .Height = Application.Height
  .Width = Application.Width
  .Left = Application.Left
  .Top = Application.Top
End With

Then, I made the UserForm semitransparent, which gives Excel’s window a dimmed appearance. The message box (or another UserForm) is displayed on top of the semitransparent UserForm.

A Puzzle on a UserForm

The example in this section is a familiar sliding puzzle, displayed on a UserForm (see Figure 15.31). This puzzle was invented by Noyes Chapman in the late 1800s. In addition to providing a few minutes of amusement, you may find the coding instructive.

The goal is to arrange the shuffled tiles (CommandButton controls) in numerical order. Click a button next to the empty space, and the button moves to the empty space. The ComboBox control lets the user choose from three configurations: 3 x 3, 4 x 4, and 5 x 5. The New button shuffles the tiles, and a Label control keeps track of the number of moves.

This application uses a class module to handle all button events (see “Handling Multiple UserForm Controls with One Event Handler,” earlier in this chapter).

The VBA code is lengthy, so it’s not listed here. Here are a few points to keep in mind when examining the code:

• The CommandButton controls are added to the UserForm via code. The number and size of the buttons are determined by the ComboBox value.
• The tiles are shuffled by simulating a few thousand random clicks on the buttons. Another option is to simply assign random numbers, but that could result in some unsolvable games.
• The blank space in the tile grid is actually a CommandButton with its Visible property set to False.
• The class module contains one event procedure (MouseUp), which is executed whenever the user clicks a tile.
• When the user clicks a CommandButton tile, its Caption is swapped with the hidden button. The code doesn’t actually move any buttons.

Video Poker on a UserForm

And finally, proof that Excel doesn’t have to be boring. Figure 15.32 shows a UserForm set up as a casino-style video poker game.

The game features:

• A choice between two games: Joker’s Wild and Jacks or Better
• A chart that shows your winning (or losing) history
• The capability to change the payoffs
• Help (displayed on a worksheet)
• An emergency button that quickly hides the UserForm

All that’s missing is the casino noise.

As you might expect, the code is much too lengthy to list here, but if you examine the workbook, you’ll find lots of useful UserForm tips — including a class module example.