Creating UserForms Programmatically | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The final topic in this chapter demonstrates how to use VBA code to create UserForms at runtime. I present two examples. One is relatively simple, and the other is quite a bit more complex.

A simple runtime UserForm example

The example in this section isn't all that useful - in fact, it's completely useless. But it does demonstrate some useful concepts. The MakeForm procedure performs several tasks :

It creates a temporary UserForm in the active workbook by using the Add method of the VBComponents collection.
It adds a CommandButton control to the UserForm by using the Designer object.
It adds an event handler procedure to the UserForm's code module ( CommandButton1_Click ). This procedure, when executed, simply displays a message box and then unloads the form.
It displays the UserForm.
It deletes the UserForm.

The net result is a UserForm that's created on the fly, put to use, and then deleted. This example and the one in the next section both blur the distinction between modifying forms at design time and modifying forms at runtime. The form is created by using design-time techniques, but it all happens at runtime.

The following shows the MakeForm procedure:

 Sub MakeForm()     Dim TempForm As Object     Dim NewButton As Msforms.CommandButton     Dim Line As Integer     Application.VBE.MainWindow.Visible = False '   Create the UserForm     Set TempForm = ThisWorkbook.VBProject. _       VBComponents.Add(3) 'vbext_ct_MSForm     With TempForm         .Properties("Caption") = "Temporary Form"         .Properties("Width") = 200         .Properties("Height") = 100     End With '   Add a CommandButton     Set NewButton = TempForm.Designer.Controls _       .Add("Forms.CommandButton.1")     With NewButton         .Caption = "Click Me"         .Left = 60         .Top = 40     End With  '   Add an event-hander sub for the CommandButton     With TempForm.CodeModule         Line = .CountOfLines         .InsertLines Line + 1, "Sub CommandButton1_Click()"         .InsertLines Line + 2, " MsgBox ""Hello!"""         .InsertLines Line + 3, " Unload Me"         .InsertLines Line + 4, "End Sub"     End With '   Show the form     VBA.UserForms.Add(TempForm.Name).Show ' '   Delete the form     ThisWorkbook.VBProject.VBComponents.Remove TempForm End Sub

CD-ROM

This example, named create userform on the fly.xlsm , is available on the companion CD-ROM.

The MakeForm procedure creates and shows the simple UserForm shown in Figure 28-9.

Figure 28-9: This UserForm and its underlying code were generated on the fly.

Note	The workbook that contains the `MakeForm` procedure does not need a reference to the VBA Extensibility Library because it declares `TempForm` as a generic `Object` (not specifically as a `VBComponent` object). Moreover, it doesn't use any built-in constants.

Notice that one of the first instructions hides the VBE window by setting its Visible property to False . This eliminates the onscreen flashing that might occur while the form and code are being generated.

A useful (but not so simple) dynamic UserForm 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 .

CD-ROM

The example in this section is available on the companion CD-ROM. The filename is ˜ getoption function.xlsm ' .

The GetOption function procedure follows .

 Function GetOption(OpArray, Default, Title)     Dim TempForm As Object     Dim NewOptionButton As Msforms.OptionButton     Dim NewCommandButton1 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.1")         With NewOptionButton             .Width = 800             .Caption = OpArray(i)             .Height = 15             .Accelerator = Left(.Caption, 1)             .Left = 8             .Top = TopPos             .Tag = i             .AutoSize = True              If Default = i Then .Value = True             If .Width > MaxWidth Then MaxWidth = .Width         End With         TopPos = TopPos + 15     Next i '   Add the Cancel button     Set NewCommandButton1 = TempForm.Designer.Controls. _       Add("Forms.CommandButton.1")     With NewCommandButton1         .Caption = "Cancel"         .Cancel = True         .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"         .Default = True         .Height = 18         .Width = 44         .Left = MaxWidth + 12         .Top = 28     End With '   Add event-hander subs for the CommandButtons     Code = ""     Code = Code & "Sub CommandButton1_Click()" & vbCrLf     Code = Code & " GETOPTION_RET_VAL=False" & vbCrLf     Code = Code & " Unload Me" & vbCrLf     Code = Code & "End Sub" & 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""" _        & " Then" & vbCrLf     Code = Code & " If ctl Then GETOPTION_RET_VAL = " _        & "ctl.Tag" & vbCrLf     Code = Code & " End If" & vbCrLf     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") = NewCommandButton1.Left + _            NewCommandButton1.Width + 10         If .Properties("Width") < 160 Then             .Properties("Width") = 160             NewCommandButton1.Left = 106             NewCommandButton2.Left = 106         End If         .Properties("Height") = TopPos + 24     End With '   Show the form     VBA.UserForms.Add(TempForm.Name).Show '   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.

USING THE GETOPTION FUNCTION

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 , none of the OptionButtons are selected (the user clicks Cancel).
Title : The text to display in the title bar of the UserForm.

HOW GETOPTION WORKS

The GetOption function performs the following operations:

Hides the VBE window to prevent any flashing that could occur when the UserForm is created or the code is added.
Creates a UserForm and assigns it to an object variable named TempForm .
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.
Adds two CommandButton controls: the OK button and the Cancel button.
Creates an event handler procedure for each of the CommandButtons.
Does some final cleanup work. It adjusts the position of the CommandButtons as well as the overall size of the UserForm.
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).
Deletes the UserForm after it's dismissed.
Returns the value of GETOPTION_RET_VAL as the function's result.

Note

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 (or presses Escape), the UserOption variable is set to False .

Notice that the Accelerator property is set to the first character of each option's caption, so the user can use an Alt+letter combination to make a choice. I made no attempt to avoid duplicate Accelerator keys, so the user may need to press the key combination multiple times to make a selection.

Figure 28-10 shows the UserForm that this function generates.

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

Note

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. Figure 28-11 shows how the form looks when the options contain more text.

Figure 28-11: The UserForm adjusts its height and width to accommodate the number of options and the length of the text.

GETOPTION EVENT-HANDLER CODE

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

Note	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