Excel Developer Tip

Excel page

Tip archives

Creating a UserForm Programmatically

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,

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.

• View the modGetOption.Bas module
• Download an Excel file that contains the module and demos

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