Chapter 13: Advanced UserForm Techniques

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

• Using an Enhanced Data Form

• Creating a moving tile puzzle

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 word vbModeless is a built-in constant that has a value of 0. Therefore, the following statement works identically:

UserForm1.Show 0

Figure 13-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.

Figure 13-1: This modeless dialog box remains visible while the user continues working.

This example, named modeless userform1.xlsm, is available on the book's website.

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.

Refer to Chapter 17 for additional information about events.

The event-handler procedures are simple:

Private Sub Workbook_SheetSelectionChange _

  (ByVal Sh As Object, ByVal Target As Range)

    Call UpdateBox

End Sub

    

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Call 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”

            Exit Sub

        End If

        .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 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 a Selection_Change event (which is located in the ThisWorkbook code module).

• The information is displayed in Label controls on the UserForm.

Figure 13-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.

Figure 13-2: This modeless UserForm displays various information about the active cell.

This example, named modeless userform2.xlsm, is available on the book's website.

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.

Refer to Chapter 27 for more information about class modules.

• 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 for

• A macro that's not initiated by a UserForm (a stand-alone progress indicator).

• A macro that is initiated by a UserForm. In this case, the UserForm uses a MultiPage control that displays the progress indicator while the macro is running.

• A macro that is initiated by a UserForm. In this case, the UserForm increases in height while the macro is running, and the progress indicator appears at the bottom of the dialog box.

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.

A progress indicator will slow down your macro a bit because of the extra overhead of having to update it. If speed is absolutely critical, you might prefer to forgo using a progress indicator.

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 13-3, displays the progress.

Figure 13-3: A UserForm displays the progress of a macro.

This example, named progress indicator1.xlsm, is available on the book's website.

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 and change its Caption property setting to Progress.

2. Add a Frame control and name it FrameProgress.

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

The label's size and placement don't matter for now.

4. Optional. Add another label above the frame to describe what's going on.

In this example, the label reads, Entering random numbers. . . .

5. Adjust the UserForm and controls so that they look something like Figure 13-4.

Figure 13-4: This UserForm will serve as a progress indicator.

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 event-handler procedures for the stand-alone progress indicator

The trick in creating the stand-alone progress indicator ‘s event handlers involves running a procedure automatically when the UserForm is displayed. One option is to use the Initialize event. However, this event occurs before the UserForm is displayed, so it's not appropriate. The Activate event, on the other hand, is triggered when the UserForm is displayed, so it's perfect for this application.

Insert the following procedure in the code window for the UserForm. This procedure simply calls a procedure named GenerateRandomNumbers when the UserForm is displayed. This procedure, which is stored in a VBA module, is the macro that runs while the progress indicator is displayed.

Private Sub UserForm_Activate()

    Call GenerateRandomNumbers

End Sub

The modified version of the GenerateRandomNumber procedure (which was presented earlier) follows. Note that additional code keeps track of the progress and stores it in a variable named PctDone.

Sub GenerateRandomNumbers()

‘   Inserts random numbers on the active worksheet

    Dim Counter As Long

    Const RowMax As Long = 500

    Const ColMax As Long = 40

    Dim r As Integer, c As Long

    Dim PctDone As Double

    

    If TypeName(ActiveSheet) <> “Worksheet” Then Exit Sub

    Cells.Clear

    Counter = 1    For r = 1 To RowMax

        For c = 1 To ColMax

            Cells(r, c) = Int(Rnd * 1000)

            Counter = Counter + 1

        Next c

        PctDone = Counter / (RowMax * ColMax)

        Call UpdateProgress(PctDone)

    Next r

    Unload UserForm1

End Sub

The GenerateRandomNumbers procedure contains two loops. In the inner loop is a call to the UpdateProgress procedure, which takes one argument (the PctDone variable, which represents the progress of the macro). PctDone will contain a value between 0 and 100.

Sub UpdateProgress(Pct)

    With UserForm1

      .FrameProgress.Caption = Format(Pct, “0%”)

      .LabelProgress.Width = Pct * (.FrameProgress.Width - 10)

      .Repaint

    End With

End Sub

Creating the start-up procedure for a stand-alone progress indicator

All that's missing is a procedure to display the UserForm. Enter the following procedure in a VBA module:

Sub ShowUserForm()

    With UserForm1

        .LabelProgress.Width = 0

        .Show

    End With

End Sub

An additional accoutrement is to make the progress bar color match the workbook's current theme. To do so, just add this statement to the ShowUserForm procedure:

.LabelProgress.BackColor = ActiveWorkbook.Theme. _

    ThemeColorScheme.Colors(msoThemeAccent1)

How the stand-alone progress indicator works

When you execute the ShowUserForm procedure, the Label object's width is set to 0. Then the Show method of the UserForm1 object displays the UserForm (which is the progress indicator). When the UserForm is displayed, its Activate event is triggered, which executes the GenerateRandomNumbers procedure. The GenerateRandomNumbers procedure contains code that calls the UpdateProgress procedure every time the r loop counter variable changes. Note that the UpdateProgress procedure uses the Repaint method of the UserForm object. Without this statement, the changes to the label would not be updated. Before the GenerateRandomNumbers procedure ends, the last statement unloads the UserForm.

To customize this technique, you need to figure out how to determine the percentage completed and assign it to the PctDone variable. This calculation will vary, depending on your application. 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 by using a MultiPage control

In the preceding example, a UserForm didn't initiate the macro. In many cases, your lengthy macro is kicked off when the user clicks the OK button on a UserForm. The technique that I describe in this section is a better solution and assumes the following:

• Your project is completed and debugged.

• Your project uses a UserForm (without a MultiPage control) to initiate a lengthy macro.

• You have a way to gauge the progress of your macro.

This book's website demonstrates this technique in the progress indicator2.xlsm file.

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 13-5).

Figure 13-5: The user specifies the number of rows and columns for the random numbers.

Modifying your UserForm for a progress indicator with 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 13-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 FrameProgress.

2. Add a Label control inside the Frame, name it LabelProgress, 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.

The easiest way to select the MultiPage control when the tabs are hidden is to use the drop-down list in the Properties window. To select a particular page, specify a Value for the MultiPage control: 0 for Page1, 1 for Page2, and so on.

Figure 13-6: Page2 of the MultiPage control will display the progress indicator.

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 UserForm1

      .FrameProgress.Caption = Format(Pct, “0%”)

      .LabelProgress.Width = Pct * (.FrameProgress.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 OKButton_Click. First, insert the following statement at the top of your procedure:

MultiPage1.Value = 1

This statement activates Page2 of the MultiPage control (the page that displays the progress indicator).

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:

Call 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 example in this section is similar to the example in the preceding section. However, this 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.

This book's website demonstrates this technique in the progress indicator3.xlsm file.

Figure 13-7 shows the UserForm in VBE.

Figure 13-7: The progress indicator will be hidden by reducing the height of the UserForm.

The Height property of the UserForm is 172. However, before the UserForm is displayed, the VBA code changes the Height to 124 (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 172 with the following statement:

Me.Height = 172

Figure 13-8 shows the UserForm with the progress indicator section unhidden.

Figure 13-8: The progress indicator in action.

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 13-9 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.

Figure 13-9: This four-step wizard uses a MultiPage control.

The wizard example in this section is available on the book's website. in a file named wizard demo.xlsm.

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

Setting up the MultiPage control for the wizard

Start with a new UserForm and add a MultiPage control. By default, this control contains two pages. Right-click the MultiPage tab and insert enough new pages to handle your wizard (one page for each wizard step). The example is a four-step wizard, so the MultiPage control has four pages. The captions of the MultiPage tabs are irrelevant because they won't be seen. The MultiPage control's Style property will eventually be set to 2 - fmTabStyleNone.

While working on the UserForm, you'll want to keep the MultiPage tabs visible to make it easier to access various pages.

Next, add the desired controls to each page of the MultiPage control. These controls will, of course, vary depending on your application. You may need to resize the MultiPage control while you work to have room for the controls.

Adding the buttons to the wizard's UserForm

Now add the buttons that control the progress of the wizard. These buttons are placed outside the MultiPage control because they're used while any of the pages are displayed. Most wizards have four buttons:

• Cancel: Cancels the wizard and performs no action.

• Back: Returns to the previous step. During Step 1 of the wizard, this button should be disabled.

• Next: Advances to the next step. During the last wizard step, this button should be disabled.

• Finish: Finishes the wizard.

In the example, these CommandButtons are named CancelButton, BackButton, NextButton, and FinishButton.

In some cases, the user is allowed to click the Finish button at any time and accept the defaults for items that were skipped. In other cases, the wizard requires a user response for some items, so the Finish button is disabled until all required input is made. The example requires an entry in the TextBox in Step 1.

Programming the wizard's buttons

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

Private Sub CancelButton_Click()

    Dim Msg As String

    Dim Ans As Integer

    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 13-10) 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.

Figure 13-10: Clicking the Cancel button displays a confirmation message box.

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

Private Sub BackButton_Click()

    MultiPage1.Value = MultiPage1.Value - 1

    UpdateControls

End Sub

    

Private Sub NextButton_Click()

    MultiPage1.Value = MultiPage1.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 BackButton and NextButton controls.

Sub UpdateControls()

    Select Case MultiPage1.Value

        Case 0

            BackButton.Enabled = False

            NextButton.Enabled = True

        Case MultiPage1.Pages.Count - 1

            BackButton.Enabled = True

            NextButton.Enabled = False

        Case Else

            BackButton.Enabled = True

            NextButton.Enabled = True

    End Select

    

‘   Update the caption

    Me.Caption = APPNAME & “ Step “ _

      & MultiPage1.Value + 1 & “ of “ _

      & MultiPage1.Pages.Count

    

‘   The Name field is required

    If tbName.Text = “” Then

        FinishButton.Enabled = False

    Else

        FinishButton.Enabled = True

    End If

End Sub

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 tbName). This field is required, so if it's empty the user can't click the Finish button. If the TextBox is empty, the FinishButton is disabled; otherwise, it's enabled.

Programming dependencies in a wizard

In most wizards, a user's response on a particular step can affect what's displayed in a subsequent step. In this example, the users indicate which products they use in Step 3 and then rate those products in Step 4. The OptionButtons for a product's rating are visible only if the users have indicated a particular product.

Programmatically, you accomplish this task by monitoring the MultiPage's Change event. Whenever the value of the MultiPage is changed (by clicking the Back or Next button), the MultiPage1_Change procedure is executed. If the MultiPage control is on the last tab (Step 4), the procedure examines the values of the CheckBox controls in Step 3 and makes the appropriate adjustments in Step 4.

In this example, the code uses two arrays of controls — one for the product CheckBox controls (Step 3) and one for the Frame controls (Step 4). The code uses a For-Next loop to hide the Frames for the products that aren't used and then adjusts their vertical positioning. If none of the check boxes in Step 3 are checked, everything in Step 4 is hidden except a TextBox that displays Click Finish to exit (if a name is entered in Step 1) or A name is required in Step 1 (if a name isn't entered in Step 1). The MultiPage1_Change procedure follows:

Private Sub MultiPage1_Change()

    Dim TopPos As Long

    Dim FSpace As Long

    Dim AtLeastOne As Boolean

    Dim i As Long

    

‘   Set up the Ratings page?

    If MultiPage1.Value = 3 Then

‘       Create an array of CheckBox controls

        Dim ProdCB(1 To 3) As MSForms.CheckBox

        Set ProdCB(1) = cbExcel

        Set ProdCB(2) = cbWord

        Set ProdCB(3) = cbAccess

        

‘       Create an array of Frame controls

        Dim ProdFrame(1 To 3) As MSForms.Frame

        Set ProdFrame(1) = FrameExcel

        Set ProdFrame(2) = FrameWord

        Set ProdFrame(3) = FrameAccess

        

        TopPos = 22

        FSpace = 8

        AtLeastOne = False

    

‘       Loop through all products

        For i = 1 To 3

            If ProdCB(i) 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

            lblHeadings.Visible = True

            Image4.Visible = True

            lblFinishMsg.Visible = False

        Else

            lblHeadings.Visible = False

            Image4.Visible = False

            lblFinishMsg.Visible = True

            If tbName = “” Then

                lblFinishMsg.Caption = _

                  “A name is required in Step 1.”

            Else

                lblFinishMsg.Caption = _

                  “Click Finish to exit.”

            End If

        End If

    End If

End Sub

Performing the task with the wizard

When the user clicks the Finish button, the wizard performs its task: transferring the information from the UserForm to the next empty row in the worksheet. This procedure, named FinishButton_Click, is straightforward. It starts by determining the next empty worksheet row and assigns this value to a variable (r). The remainder of the procedure simply translates the values of the controls and enters data into the worksheet.

Private Sub FinishButton_Click()

    Dim r As Long

    r = Application.WorksheetFunction. _

      CountA(Range(“A:A”)) + 1

    

‘   Insert the name

    Cells(r, 1) = tbName.Text

    

‘   Insert the gender

    Select Case True

        Case obMale: Cells(r, 2) = “Male”

        Case obFemale: Cells(r, 2) = “Female”

        Case obNoAnswer: Cells(r, 2) = “Unknown”

    End Select

    

‘   Insert usage

    Cells(r, 3) = cbExcel

    Cells(r, 4) = cbWord

    Cells(r, 5) = cbAccess

    

‘   Insert ratings

    If obExcel1 Then Cells(r, 6) = “”

    If obExcel2 Then Cells(r, 6) = 0

    If obExcel3 Then Cells(r, 6) = 1

    If obExcel4 Then Cells(r, 6) = 2

    If obWord1 Then Cells(r, 7) = “”

    If obWord2 Then Cells(r, 7) = 0

    If obWord3 Then Cells(r, 7) = 1

    If obWord4 Then Cells(r, 7) = 2

    If obAccess1 Then Cells(r, 8) = “”

    If obAccess2 Then Cells(r, 8) = 0

    If obAccess3 Then Cells(r, 8) = 1

    If obAccess4 Then Cells(r, 8) = 2

    

‘   Unload the form

    Unload Me

End Sub

After you test your wizard, and everything is working properly, you can set the MultiPage control's Style property to 2 - fmTabStyleNone to hide the tabs.

Emulating the MsgBox Function

The VBA MsgBox function (discussed in Chapter 10) 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.

The point of this exercise is not to create an alternative messaging function. Rather, it's to demonstrate how to develop a complex function that also incorporates a UserForm. However, some people might like the idea of being able to customize their messages. If so, you'll find that this function is easy to customize. For example, you can change the font, colors, button text, and so on.

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.

If you're not familiar with the VBA MsgBox function, consult the Help system to become acquainted with its arguments.

MsgBox emulation: MyMsgBox code

The MyMsgBox function uses a UserForm named MyMsgBoxForm. The function itself, which follows, is short. The bulk of the work is done in the UserForm_Initialize procedure.

Public Prompt1 As String

Public Buttons1 As Integer

Public Title1 As String

Public UserClick As Integer

    

Function MyMsgBox(ByVal Prompt As String, _

  Optional ByVal Buttons As Integer, _

  Optional ByVal Title As String) As Integer

    Prompt1 = Prompt

    Buttons1 = Buttons

    Title1 = Title

    MyMsgBoxForm.Show

    MyMsgBox = UserClick

End Function

The complete code for the MyMsgBox function is too lengthy to list here, but it's available in a workbook named msgbox emulation.xlsm, available on the book's website. The workbook is set up so that you can easily try various options.

Figure 13-11 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.

Figure 13-11: The result of the MsgBox emulation function.

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 MyMsgBoxForm:

With MyMsgBoxForm

    .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

In The MyMsgBox function, note the use of four Public variables. The first three (Prompt1, Buttons1, and Title1) represent the arguments that are passed to the function. The other variable (UserClick) represents the values returned by the function. The UserForm_Initialize procedure needs a way to get this information and send it back to the function, and using Public variables is the only way to accomplish that.

The UserForm (shown in Figure 13-12) 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.

Figure 13-12: The UserForm for the MyMsgBox function.

I originally used Image controls to hold the four icons, but the images displayed with a faint outline. I switched to Label controls because the image is not displayed with an outline.

The code in the UserForm_Initialize procedure examines the arguments and 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

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.

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).

The challenge was pulling three pieces of information from a single number. The solution involves converting the argument to a binary number and then examining specific bits. For example, 547 in binary is 1000100011. Binary digits 4 through 6 determine the image displayed; digits 8 through 10 determine which buttons to display; and digits 1 and 2 determine which button is the default button.

Using the MyMsgBox function

To use this function in your own project, export the MyMsgBoxMod module and the MyMsgBoxForm UserForm. Then import these two files into your project. You can then use the MyMsgBox function in your code just as you'd use the MsgBox function.

A UserForm with Movable Controls

The UserForm shown in Figure 13-13 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.

Figure 13-13: You can drag and rearrange the three Image controls by using the mouse.

This example is available on the book's website in a file named move controls.xlsm.

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 13-14 shows a UserForm with no title bar.

Figure 13-14: This UserForm lacks a title bar.

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

Both examples are in a workbook named no title bar.xlsm, which is available on the book's website. Also available is another version of the splash screen example presented in Chapter 12. This version, named splash screen2.xlsm, displays the UserForm without a title bar.

Figure 13-15: Another UserForm without a title bar.

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.

Because the FindWindowA function uses the UserForm's caption, this technique won't work if the Caption property is set to an empty string.

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 13-16 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.

Figure 13-16: A UserForm set up to function as a toolbar.

This example, named simulated toolbar.xlm, is available on the book's website.

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

• The controls aren't aligned.

• The images displayed are not the final images

• The UserForm isn't the final size.

• The title bar is the standard size.

Figure 13-17: The UserForm that simulates a toolbar.

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

Image1.Picture = Application.CommandBars. _

   GetImageMso(“ReviewAcceptChange”, 32, 32)

See Chapter 20 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

In Office 2013, task panes take on an expanded role and are used for adjusting formatting for many objects, including charts and images. The task panes also have a new look.

I spent some time to try to emulate the look of a task pane in a UserForm. The result is shown in Figure 13-18. The example is the same as the modeless UserForm example at the beginning of the chapter (refer to Figure 13-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.

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)

Figure 13-18: A UserForm designed to look like a task pane.

I think I managed to capture 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).

This example, named emulate task pane.xlm, is available on the book's website.

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.

Credit for this technique goes to Andy Pope, an Excel expert and Microsoft MVP who lives in the UK. Andy is one of the most creative Excel developers I've ever met. For a real treat (and lots of interesting downloads), visit his website at http://andypope.info.

Figure 13-19 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 13-19: This UserForm is resizable.

Figure 13-20 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.

Figure 13-20: The UserForm after it was increased.

This example is available on the book's website in the resizable userform.xlsm file.

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

Although the Label control is added at runtime, the event-handler code for the object is contained in the module. Including code for an object that doesn't exist does not present a problem.

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 13-21 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.

Figure 13-21: VBA code converts Label control movements into new Width and Height properties for the UserForm.

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 - 22

        .Height = Me.Height - 100

    End With

    On Error GoTo 0

    

‘   Adjust the Close Button

    With CloseButton

        .Left = Me.Width - 70

        .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.

This example, named multiple buttons.xlsm, is available on the book's website.

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

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.

Figure 13-22: Multiple CommandButtons with a single event-handler procedure.

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.

You can adapt this technique to work with other types of controls. You need to change the type name in the Public WithEvents declaration. For example, if you have OptionButtons instead of CommandButtons, use a declaration statement like this:

Public WithEvents ButtonGroup As MsForms.OptionButton

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 Integer

    Dim ctl As Control

    

‘   Create the Button objects

    ButtonCount = 0

    For Each ctl In UserForm1.Controls

        If TypeName(ctl) = “CommandButton” Then

            ‘Skip the OKButton

            If ctl.Name <> “OKButton” 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 OKButton 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 13-23 shows an example of the message displayed when a button is clicked.

Figure 13-23: The ButtonGroup_Click procedure describes the button that was 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:

Public ColorValue As Variant

    

Function GetAColor() As Variant

    UserForm1.Show

    GetAColor = ColorValue

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 13-24, 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 ScrollBarRed_Change()

    LabelRed.BackColor = RGB(ScrollBarRed.Value, 0, 0)

    Call UpdateColor

End Sub

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

Figure 13-24: This dialog box lets the user select a color by specifying the red, green, and blue components.

This example, named getacolor function.xlsm, is available on the book's website.

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”, ScrollBarRed.Value

SaveSetting APPNAME, “Colors”, “BlueValue”, ScrollBarBlue.Value

SaveSetting APPNAME, “Colors”, “GreenValue”, ScrollBarGreen.Value

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

ScrollBarRed.Value = GetSetting(APPNAME, “Colors”, “RedValue”, 128)

ScrollBarGreen.Value = GetSetting(APPNAME, “Colors”, “GreenValue”, 128)

ScrollBarBlue.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 13-25 shows the Registry data, displayed with the Windows Regedit.exe program.

Figure 13-25: The user's ScrollBar values are stored in the Windows Registry and retrieved the next time the GetAColor function is used.

To learn more about how Excel uses colors, refer to Chapter 28.

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 13-26 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.

This workbook is available on the book's website in the chart in userform.xlsm file.

Figure 13-26: With a bit of trickery, a UserForm can display “live” charts.

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.

Saving a chart as a GIF file

The following code demonstrates how to create a GIF file (named temp.gif) from a chart (in this case, the first chart object on the sheet named Data):

Set CurrentChart = Sheets(“Data”).ChartObjects(1).Chart

Fname = ThisWorkbook.Path & “ emp.gif”

CurrentChart.Export FileName:=Fname, FilterName:=”GIF”

Changing the Image control's Picture property

If the Image control on the UserForm is named Image1, the following statement loads the image (represented by the Fname variable) into the Image control:

Image1.Picture = LoadPicture(Fname)

This technique works fine, but you may notice a slight delay when the chart is saved and then retrieved. On a fast system, however, this delay is hardly noticeable.

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 13-27 shows an example of a UserForm with a transparency level of about 128.

This workbook is available on the book's website. The filename is semi-transparent userform.xlsm.

Figure 13-27: A semitransparent UserForm.

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 13-28 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.

This workbook is available on the book's website in the excel light-box.xlsm file.

Figure 13-28: Creating a light-box effect in Excel.

An Enhanced Data Form

The example in this section is probably one of the more complex UserForms that you'll encounter. I designed it as a replacement for Excel's built-in Data Form, which is shown in Figure 13-29.

Displaying Excel's Data Form is not easy in recent versions of Excel. This command isn't part of Excel's user interface, so you need to add the command to the Ribbon or to the Quick Access toolbar. To add it to the Quick Access toolbar, right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. Then, in the Excel Options dialog box, add the Form command from the Commands Not in the Ribbon group.

Like Excel's Data Form, my Enhanced Data Form works with a list in a worksheet. But as you can see in Figure 13-30, it has a dramatically different appearance and offers several advantages.

Figure 13-29: Excel's Data Form.

Figure 13-30: My Enhanced Data Form.

About the Enhanced Data Form

The Enhanced Data Form features the enhancements listed in Table 13-1.

Table 13-1: Comparing the Enhanced Data Form with the Excel Data Form

Enhanced Data Form	Excel Data Form
Handles any number of records and fields.	Limited to 32 fields.
Dialog box can be displayed in any size that you like and can be resized by the user.	Dialog box adjusts its size based on the number of fields. In fact, it can take up the entire screen!
Fields can consist of either InputBox or ComboBox controls.	Uses only InputBox controls.
Can modify the width of the descriptive column headers.	Can't change column header fields.
Can easily change the language used in the dialog box (VBA password required).	Can't change language.
Record displayed in the dialog box is always visible on-screen and is highlighted so that you know exactly where you are.	Doesn't scroll the screen for you and doesn't highlight the current record.
At start-up, the dialog box always displays the record at the active cell.	Always starts with the first record in the database.
When you close the dialog box, the current record is selected for you.	Doesn't change your selection when you exit.
Lets you insert a new record at any position in the database.	Adds new records only at the end of the database.
Includes an Undo button for Data Entry, Insert Record, Delete Record, and New Record.	Includes only a Restore button.
Search criteria are stored in a separate panel, so you always know exactly what you're searching for.	The search criteria aren't always apparent.
Supports approximate matches while searching (*, ?, and #).	Doesn't support wildcard characters.
The complete VBA source code is available, so you can customize it to your needs.	Isn't written in VBA and can't be customized.

The Enhanced Data Form is a commercial product (sort of). Versions for Excel 97 and later are available on the book's website. These files may be distributed freely.

If you'd like to customize the code or UserForm, access to the complete VBA source is available for a modest fee. You can find out the details at http://spreadsheetpage.com.

Installing the Enhanced Data Form add-in

To try out the Enhanced Data Form, install the add-in:

1. Download the dataform3.xlam file to your hard drive.

2. In Excel, press Alt+TI to display the Add-Ins dialog box.

3. In the Add-Ins dialog box, click Browse and locate the dataform3.xlam file in the directory from Step 1.

After performing these steps, you can access the Enhanced Data Form by choosing Data⇒DataForm⇒J-Walk Enhanced DataForm. You can use the Enhanced Data Form to work with any worksheet list or table.

A Puzzle on a UserForm

The example in this section is a familiar sliding puzzle, displayed on a UserForm (see Figure 13-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.

Figure 13-31: A sliding tile puzzle in a UserForm.

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.

This workbook, named sliding tile puzzle.xlsm, is available on the book's website.

Video Poker on a UserForm

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

Figure 13-32: A feature-packed 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.

This workbook, named video poker.xlsm, is available on the book's website.

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.