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 :

  1. It creates a temporary UserForm in the active workbook by using the Add method of the VBComponents collection.

  2. It adds a CommandButton control to the UserForm by using the Designer object.

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

  4. It displays the UserForm.

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

HOW GETOPTION WORKS

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 CommandButton controls: 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.

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

Related

Категории