General Userform Tips

Category: UserForms | [Item URL]

One of the most useful features in Excel is the ability to create custom dialog boxes (UserForms). Before embarking on this task, ask yourself if a custom dialog box is really necessary. In some cases you may be able to use one of the following options to get user input:

  • VBA's MsgBox function
  • VBA's InputBox function (always returns a string)
  • Excel's InputBox function (you can specify the type of data returned)
  • Excel's GetOpenFilename method
  • Excel's GetSaveAsFilename method
  • Excel's FileDialog object (Excel 2002 only)

And, don't forget that your VBA code can display most of Excel's built-in dialog boxes. For example, the following subroutine displays the Move or Copy dialog (used to move or copy a sheet):

Sub ShowMoveCopy()
End Sub

When designing your userForm, a good rule of thumb is to make the dialog boxes look and work like those that are built into Excel. If you're a beginner, you may find it instructive to try to recreate a particular Excel dialog box. You may find a few situations in which a built-in dialog box can't be recreated exactly.


Before you unleash your UserForm, give it one final check using the following checklist.

  1. Are the controls aligned with each other?
  2. Are similar controls the same size?
  3. Are controls evenly spaced?
  4. Is the dialog box too overwhelming? If so, you may want to use a series of dialogs (like a Wizard), or use a MultiPage control.
  5. Can every control be accessed with an accelerator key?
  6. Are any of the accelerator keys duplicated?
  7. Are the controls grouped logically (by function)?
  8. Is the tab order set correctly? The user should be able to tab through the dialog box and access the controls sequentially.
  9. If the UserForm will be stored in an add-in, did you test it thoroughly after creating the add-in? It's important to remember that an add-in will never be the active workbook.
  10. Will your VBA code take appropriate action if the UserForm is canceled, or if the user presses Esc?
  11. Are there any misspellings in the text? Excel's spelling checker doesn't work with UserForms.
  12. Does the UserForm have an appropriate caption?
  13. If applicable, will your UserForm fit on the screen in lower screen resolutions?
  14. If your UserForm will be used in multiple versions of Excel, have you tested the application on all versions?
  15. If you use help, is the help topic correct? And does it explain all the controls?

Displaying A Progress Indicator

Category: UserForms | [Item URL]

A companion file is available: Click here to download

A common question among Excel developers is, How can I use a custom dialog box to display the progress of a lengthy macro?

This document describes how to create an attractive progress indicator with minimal effort.

Creating the UserForm

Follow the steps below to create the progress indicator UserForm.

  1. Insert a new UserForm and change its Caption to Progress.
  2. Add a Frame control and name it FrameProgress.
  3. Add a Label control inside of the Frame and name it LabelProgress. Remove the Label's caption, and make its background color red.
  4. Add another label (option) to describe what's going on.
  5. Adjust the form and controls so they look like this:

Creating the Event-handler subroutines

The trick here involves running a subroutine automatically when the dialog box is displayed. Since the Initialize event occurs before the dialog box is actually show, you must use the Activate event. Insert the following subroutine in the Code window for the UserForm. This subroutine simply calls the Main subroutine (stored in a VBA module) when the UserForm is displayed.

Private Sub UserForm_activate()
    Call Main
End Sub

The Main subroutine is listed below. This demo routine simply inserts random numbers into the active worksheet. As it does so, it changes the width of the Label control and displays the percent completed in the Frame's caption. You will, of course, substitute your own subroutine. And you'll need to figure out how to determine the progress complete.

Sub Main()
'   Inserts random numbers on the active worksheet
    Dim Counter As Integer
    Dim RowMax As Integer, ColMax As Integer
    Dim r As Integer, c As Integer
    Dim PctDone As Single
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    Application.ScreenUpdating = False
    Counter = 1
    RowMax = 100
    ColMax = 25
    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)
        With UserForm1
            .FrameProgress.Caption = Format(PctDone, "0%")
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
'       The DoEvents statement is responsible for the form updating
    Next r
    Unload UserForm1
End Sub

Creating the start-up subroutine

All that's missing is a subroutine to display the dialog box. Enter the following subroutine in a VBA module.

Sub ShowDialog()
    UserForm1.LabelProgress.Width = 0
End Sub

How it works

When you execute the ShowDialog subroutine, the Label object's width is set to 0. Then the Show method of the UserForm1 object displays the dialog box. When the dialog box is displayed, its Activate event is triggered, which executes the Main subroutine. The Main subroutine periodically updates the width of the Label. Notice the DoEvents statement. Without this statement, changes to the Label are not visible. Before the subroutine ends, the last statement unloads the UserForm object.


This is definitely a slick technique, but you should be aware that it may slow down your macro a bit due to the extra overhead of updating the progress indicator. In many cases, however, it is not even noticeable.

Importing And Exporting Userforms

Category: UserForms | [Item URL]

If you've worked with UserForms, you've undoubtedly noticed that inserting a new UserForm results in an empty dialog box. You might prefer to add a UserForm that already has some controls on it (for example, an OK button and a Cancel button).

In this document I describe a simple technique that lets you create a new "default" UserForm that contains these two buttons. The procedure can be adapted for other controls.

Creating the default UserForm

Follow these steps to create a UserForm.

  1. Start with a blank workbook.
  2. Press Alt+F11 to activate the Visual Basic Editor (VBE)
  3. In the Project window, select the blank workbook
  4. Select the Insert UserForm command. An empty UserForm is added to the project.
  5. Use the Toolbox and add a CommandButton to the form.
  6. Change the following properties for the CommandButton:

    Name: OKButton
    Caption: OK
    Default: True

  7. Use the Toolbox and add a second CommandButton to the form.
  8. Change the following properties for this CommandButton:

    Name: CancelButton
    Caption: Cancel
    Cancel: True

  9. Double-click the Cancel button to activate the Code window for the UserForm.
  10. Modify the CancelButton_Click subroutine as follows:
Private Sub Cancel_Button_Click()
    Unload Me
End Sub

Exporting the UserForm

The next step is to export this UserForm.

  1. Make sure the UserForm is selected in the Project window.
  2. Select the File Export File command.
  3. Enter a name for the exported UserForm. Use a descriptive name, like NewDefaultForm

The preceding steps saved the UserForm and code to a file.

Importing the UserForm

When you need to add a new UserForm to a project, you can save time by importing the file you saved.

  1. Make sure your project is selected in the Project window.
  2. Select the File Import File command.
  3. Locate the file you exported in the previous section.
  4. Use the Properties window to give the new form a descriptive name. This step is necessary if you later decide to import the file again to add another UserForm.

Handle Multiple Userform Buttons With One Subroutine

Category: UserForms | [Item URL]

A companion file is available: Click here to download

When you create a UserForm, every control on the form must have its own event handler procedure. For example, if a UserForm has 12 CommandButtons, you need 12 procedures to handle the click events for those buttons.

This tip describes a way around this limitation by using a Class Module to define a new class.


  1. Create your UserForm as usual, and add several CommandButtons.
  2. Insert a Class Module and enter the following code. You will need to customize the ButtonGroup_Click subroutine.
Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
    MsgBox "Hello from " & ButtonGroup.Name
End Sub
  1. Insert a normal module and enter the following code. In this code, I exclude a button named OKButton from the "button group." Therefore, clicking the OK Button does not execute the ButtonGroup_Click subroutine.
Option Explicit
Dim Buttons() As New Class1

Sub ShowDialog()
    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
            If ctl.Name <> "OKButton" Then 'Skip the OKButton
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
               Set Buttons(ButtonCount).ButtonGroup = ctl
            End If
        End If
    Next ctl
End Sub
  1. Execute the ShowDialog subroutine to display the UserForm. Clicking any of the CommandButtons (except the OKButton) executes the ButtonGroup_Click subroutine.

Note: To use this technique with other types of controls, change the WithEvents statement. For example:

   Public WithEvents LabelGroup As MSForms.Label

Notice that you must qualify the Label object because Excel also has an object named Label. Also, you will need to make appropriate changes throughout the ShowDialog procedure.

Filling A Listbox With Unique Items

Category: UserForms | [Item URL]

A companion file is available: Click here to download

When you display a list of items in a ListBox, you generally don't want to show duplicate items. This tip describes a clever way of filling a UserForm with unique items from a list. This technique is adapted from a tip by J.G. Hussey, published in Visual Basic Programmer's Journal.

How it works

This tip uses a Collection object, and relies on the fact that VBA generates an error if you attempt to add an item to a collection when the item already exists in the collection. The trick is to build the collection by adding all items to it, and ignore the errors that may occur. The result is a collection of unduplicated items.


I created an example to demonstrate the technique. The items (105 of them) are stored in Column A of a worksheet. Many of these items are duplicated. The RemoveDuplicates subroutine, listed below, builds a collection that consists of the unique items in the list. It then transfers the items to a ListBox on a UserForm.


Following is the VBA listing.

Option Explicit
'   This example is based on a tip by J.G. Hussey,
'   published in "Visual Basic Programmer's Journal"

Sub RemoveDuplicates()
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
'   The items are in A1:A105
    Set AllCells = Range("A1:A105")

'   The next statement ignores the error caused
'   by attempting to add a duplicate key to the collection.
'   The duplicate is not added - which is just what we want!
    On Error Resume Next
    For Each Cell In AllCells
        NoDupes.Add Cell.Value, CStr(Cell.Value)
'       Note: the 2nd argument (key) for the Add method must be a string
    Next Cell

'   Resume normal error handling
    On Error GoTo 0

'   Sort the collection (optional)
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i

'   Add the sorted, non-duplicated items to a ListBox
    For Each Item In NoDupes
        UserForm1.ListBox1.AddItem Item
    Next Item

'   Show the UserForm
End Sub

Creating A Color Picker Dialog Box

Category: UserForms | [Item URL]

A companion file is available: Click here to download

This tip demonstrates a technique to display a dialog box that allows the user to select a color.

How it works

The example file contains the following:

  • A UserForm (UserForm1) that contains a dialog box with 56 buttons.
  • A Class Module (Class1) that defines a ColorButton Class.
  • A VBA module (Module1) that contains a function (GetAColor).
  • Two example subroutines that demonstrate the GetAColor function.

The GetAColor function sets up the dialog box and display it (see the figure below). The function returns the color value of the selected button. If the user clicks Cancel, the GetAColor function returns False. As the user moves the mouse pointer over the color buttons, the Color Sample image displays the color.

Using the function

To use the GetAColor function in your own workbooks, export UserForm1, Module1, and Class1, and then import them into your workbook. Write your own subroutine that calls the GetAColor function.

Displaying A Chart In A Userform

Category: UserForms | [Item URL]

A companion file is available: Click here to download

One of my favorite Excel tricks is to display a chart in a UserForm. You might think that this would be easy: Just add a linked picture of the chart to the UserForm. Unfortunately, that's not possible.

This tip describes a workaround that involves saving the chart as a GIF file, and then displaying the GIF in an Image control. This ensures that the UserForm always displays the current version of the chart.

You might notice a slight delay as the chart is saved and then retrieved. On a fast system, however, this delay is barely noticeable.

How it works

To set this up:

  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 the LoadPicture function to do this.
  4. Add other bells and whistles as desired. For example, the demo file displays multiple charts, and the user can cycle through them.

Saving a chart as a GIF file

The code below demonstrates how to create a GIF file (named temp.gif) from a chart (the first chart object on Sheet1).

    Set CurrentChart = Sheets("Sheet1").ChartObjects(1).Chart
    Fname = ThisWorkbook.Path & "\temp.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 statement below loads the image (represented by the Fname variable) into the Image control.

        Image1.Picture = LoadPicture(Fname)

Adding A Hyperlink To A Userform

Category: UserForms | [Item URL]

A companion file is available: Click here to download

You probably know that the Insert Hyperlink command (or Ctrl+K) lets you insert a hyperlink into any cell. But if you've tried to add a hyperlink to a UserForm, you may have come up empty-handed.

There is no direct way to add a hyperlink to a UserForm, but you can simulate one by using the techniques described here. The figure below shows an example.

(Yes, this is a REALLY old screen shot!)

  1. Add a Label object and enter some text for its Caption
  2. Make the Label blue and underlined so it resembles a typical hyperlink. You might also want to set its font to Courier New.
  3. None of the standard mouse pointers resembles a pointing hand, so set the Label's MousePointer property to: 99 - fmMousePointerCustom
  4. Specify the cursor file for the Label's MouseIcon image. If you don't have a cursor file that resembles a pointing hand, click here to download a file named hand.cur.
  5. Double-click the Label and enter an event-handler subroutine for its Click event. The FollowHyperlink method is what makes it work. Here's an example of such a subroutine:
Private Sub Label1_Click()
    Link = ""
    On Error GoTo NoCanDo
    ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
    Unload Me
    Exit Sub
    MsgBox "Cannot open " & Link
End Sub

To create a "mail to" hyperlink, use a statement like this:

    Link = ""

Creating A Userform Programmatically

Category: UserForms | [Item URL]

A companion file is available: Click here to download

Many VBA programmers don't realize it, but it's possible to create a UserForm using VBA. In addition, you can write code to add controls to the UserForm, and even create event-handler procedures in the UserForm's code module.

This is possible because of the Visual Basic Integrated Development Environment (the VBIDE). The VBIDE is described in the online help, but it's very sketchy and provides few examples. The example in this document may help you understand how the VBIDE works.

The GetOption Function

This document describes a function named GetOption, which takes three arguments:

  • OpArray: An string array that holds the items to be displayed in the form as OptionButtons
  • Default: An integer that specified the default OptionButton that is selected when the UserForm is displayed. If zero, none of the OptionsButtons is displayed.
  • Title: Text to display in the title bar of the UserForm

When the function is called it performs the following tasks:

  1. It creates a new UserForm.
  2. It adds OptionButton controls to the UserForm, the number of which corresponds to the number of elements in the OpArray argument.
  3. It adds two CommandButton controls to the UserForm (OK and Cancel).
  4. It creates two subroutines to handle the Click events for the CommandButtons.
  5. Adjusts the size of the UserForm to accommodate the OptionButtons.
  6. It displays the UserForm and waits for the user's response.
  7. It returns the index number of the selected item to the calling procedure -- or it returns False if the UserForm was canceled.
  8. It deletes the UserForm.

The GetOption function is very useful for soliciting information from a user, and is often an excellent alternative to MsgBox or InputBox -- and it's certainly easier than creating a custom UserForm.

Contrary to what you might expect, this process is very fast -- virtually instantaneous on my system.

Using the GetOption function

The function is completely self-contained in a module. Consequently, it can be added to any existing project by importing the *.BAS file or by copying and pasting the code.

The example below is a simple subroutine that demonstrates the use of GetOption. It creates a 12-item array (Ops) that consists of the month names. It then calls the GetOption function, passing the following arguments: The 12-item array, a literal 1 (the first item is the default), and a literal string ("Select a month").

Sub DemoGetOption()
    Dim Ops(1 To 12) As String
'   Create an array of month names
    For i = 1 To 12
        Ops(i) = Format(DateSerial(1, i, 1), "mmmm")
    Next i
    UserChoice = GetOption(Ops, 1, "Select a month")
    MsgBox UserChoice
End Sub

The figure below shows the UserForm as it is displayed to the user. When the user clicks OK, the GetOption function returns a value between 1 and 12. If the user clicks Cancel, the function returns False, (3506 bytes)

View or Download

Click the link below to download modGetOption.Bas. This is a text file that can be imported directly into a VBA project. In the VB Editor, activate your project, then select File Import File (or press Ctrl+M). Locate the modGetOption.Bas file and click OK. The module will be added to your project.

: It is not necessary to add a reference to the Visual Basic Extensibility Library in order to use this function.

Disabling A Userform’s Close Button

Category: UserForms | [Item URL]

Every UserForm that you create contains a Close button ("x") in its title bar. Clicking the Close button (or pressing Alt+F4) closes the UserForm.

In some cases, you may not want to allow the user to close the UserForm in this manner. There's no easy way to disable the Close button, but a few lines of VBA code will prevent it from being used.

Activate the code module for your UserForm, and enter the following procedure:

Private Sub UserForm_QueryClose _
  (Cancel As Integer, CloseMode As Integer)
'   Prevents use of the Close button
    If CloseMode = vbFormControlMenu Then
        MsgBox "Clicking the Close button does not work."
        Cancel = True
    End If
End Sub

When the user clicks the Close button, a message box appears and the UserForm remains open.

Page 9 of 17 pages
[Previous page]   [Next page]

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy