A useful but not so simple dynamic User Form example

The example in this section is both instructive and useful. It consists of a function named GetOption that displays a UserForm. Within this UserForm are a number of OptionButtons, whose captions are specified as arguments to the function. The function returns a value that corresponds to the OptionButton selected by the user. Listing 28-7 shows the complete function.

Listing 28-7: A Dynamically Generated Option Button Form

Function GetOption(OpArray, Default, Title) Dim TempForm As Object

Dim NewOptionButton As Msforms.OptionButton Dim NewCommandButtonl As Msforms.CommandButton Dim NewCommandButton2 As Msforms.CommandButton Dim i As Integer, TopPos As Integer Dim MaxWidth As Long Dim Code As String

' Hide VBE window to prevent screen flashing Application.VBE.MainWindow.Visible = False

' Create the UserForm Set TempForm = _

ThisWorkbook.VBProject.VBComponents.Add(3) TempForm.Properties("Width") = 800

' Add the OptionButtons TopPos = 4

MaxWidth = 0 'Stores width of widest OptionButton For i = LBound(OpArray) To UBound(OpArray)

Set NewOptionButton = TempForm.Designer.Controls. _

Add("forms.OptionButton.l") With NewOptionButton .Width = 800 .Caption = OpArray(i) .Height = l5 .Left = 8 .Top = TopPos .Tag = i

.AutoSize = True

If Default = i Then .Value = True If .Width > MaxWidth Then MaxWidth = .Width

Listing 28-7 (Continued)

End With

TopPos = TopPos + 15 Next i

' Add the Cancel button

Set NewCommandButton1 = TempForm.Designer.Controls.

Add("forms.CommandButton.1") With NewCommandButton1 .Caption = "Cancel" .Height = 18 .Width = 44 .Left = MaxWidth + 12 .Top = 6 End With

Add the OK button

Set NewCommandButton2 = TempForm.Designer.Controls.

Add("forms.CommandButton.1") With NewCommandButton2 .Caption = "OK" .Height = 18 .Width = 44 .Left = MaxWidth + 12 .Top = 28 End With

Add event-hander subs for the CommandButtons

Code = Code & "Sub CommandButton1_Click()" & vbCrLf

Code = Code & " GETOPTION_RET_VAL=False" & vbCrLf

Code = Code & " Unload Me" & vbCrLf

Code = Code & "Sub CommandButton2_Click()" & vbCrLf

Code = Code & " Dim ctl" & vbCrLf

Code = Code & " GETOPTION_RET_VAL = False" & vbCrLf

Code = Code & " For Each ctl In Me.Controls" & vbCrLf

Code = Code & " If TypeName(ctl) = ""OptionButton

Code = Code & " If ctl Then GETOPTION_RET_VAL = "

Code = Code & " Next ctl" & vbCrLf

Code = Code & " Unload Me" & vbCrLf Code = Code & "End Sub"

With TempForm.CodeModule

.InsertLines .CountOfLines + 1, Code End With

' Adjust the form With TempForm

.Properties("Caption") = Title

.Properties("Width") = NewCommandButtonl.Left + _

NewCommandButtonl.Width + 10 If .Properties("Width") < 160 Then .Properties("Width") = 160 NewCommandButtonl.Left = 106 NewCommandButton2.Left = 106 End If

.Properties("Height") = TopPos + 24 End With

' Show the form


' Delete the form

ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

' Pass the selected option back to the calling procedure

GetOption = GETOPTION_RET_VAL End Function

The GetOption function is remarkably fast, considering all that's going on behind the scenes. On my system, the form appears almost instantaneously. The UserForm is deleted after it has served its purpose.


The GetOption function takes three arguments:

♦ OpArray: A string array that holds the items to be displayed in the form as OptionButtons.

♦ Default: An integer that specifies the default OptionButton that is selected when the UserForm is displayed. If 0, none of the OptionButtons are selected.

♦ Title: The text to display in the title bar of the UserForm.


The GetOption function performs the following operations:

1. Hides the VBE window to prevent any flashing that could occur when the UserForm is created or the code is added.

2. Creates a UserForm and assigns it to an object variable named TempForm.

3. Adds the OptionButton controls by using the array passed to the function via the OpArray argument. It uses the Tag property of the control to store the index number. The Tag setting of the chosen option is the value that's eventually returned by the function.

4. Adds two CommandButtons: the OK button and the Cancel button.

5. Creates an event handler procedure for each of the CommandButtons.

6. Does some final cleanup work. It adjusts the position of the CommandButtons as well as the overall size of the UserForm.

7. Displays the UserForm. When the user clicks OK, the CommandButton1_ Click procedure is executed. This procedure determines which OptionButton is selected and also assigns a number to the GETOPTION_ RET_VAL variable (a Public variable).

8. Deletes the UserForm after it's dismissed.

9. Returns the value of GETOPTION_RET_VAL as the function's result.

A significant advantage of creating the UserForm on-the-fly is that the function is self-contained in a single module and doesn't even require a reference to the VBA Extensibility Library. Therefore, you can simply export this module (which is named modOptionsForm) and then import it into any of your workbooks, thus giving you access to the GetOption function.

The following procedure demonstrates how to use the GetOption function. In this case, the UserForm presents five options (contained in the Ops array).

Sub TestGetOption() Dim Ops(1 To 5) Dim UserOption Ops(1) = "North" Ops(2) = "South" Ops(3) = "West" Ops(4) = "East" Ops(5) = "All Regions"

UserOption = GetOption(Ops, 5, "Select a region") Debug.Print UserOption MsgBox Ops(UserOption) End Sub

The UserOption variable contains the index number of the option selected by the user. If the user clicks Cancel, the UserOption variable is set to False. Figure 28-9 shows the UserForm that this function generated.

Figure 28-9: The GetOption function generated this UserForm.

Figure 28-9: The GetOption function generated this UserForm.

The UserForm adjusts its size to accommodate the number of elements in the array passed to it.Theoretically,the UserOption function can accept an array of any size. Practically speaking, however,you'll want to limit the number of options to keep the UserForm at a reasonable size.


Following are the event handler procedures for the two CommandButtons. This is the code generated within the GetOption function and placed in the code module for the temporary UserForm.

Sub CommandButton1_Click() GETOPTION_RET_VAL = False Unload Me End Sub

Sub CommandButton2_Click() Dim ctl

GETOPTION_RET_VAL = False For Each ctl In Me.Controls

If TypeName(ctl) = "OptionButton" Then

If ctl Then GETOPTION_RET_VAL = ctl.Tag End If Next ctl Unload Me End Sub

Because the UserForm is deleted after it's used, you can't see what it looks like in the VBE. So if you'd like to view the UserForm, convert the following instruction to a comment by typing an apostrophe (') in front of it:

ThisWorkbook.VBProject.VBComponents.Remove _ VBComponent:=TempForm

Chapter 29

+3 -5

Post a comment