Adding User Forms at Run time
I am trying to create a new user at run time (on-the-fly) but not having any success at showing the new form.
I have used the discussion in Chapter 26 as a basis for my code and also Chapter 28 of "Excel 2007 Power Programming with VBA" by John Walkenback.
I start by creating a new workbook to which to add the form to, add the form and a couple of controls, and then destroy the workbook (and form) at the end. Here is the code:
Public Sub CreateRunTimeForm()
'Create a temporary workbook for the new form
Dim tempWorkbook As Workbook
Set tempWorkbook = Workbooks.Add
Dim myLabel As String
myLabel = "My Option Button"
'Add user form
Dim myNewForm As Object
Set myNewForm = tempWorkbook.VBProject.VBComponents.Add(vbext_ct_M SForm)
Dim myFrame As MSForms.Frame
Set myFrame = myNewForm.Designer.Controls.Add("Forms.Frame.1")
Dim myButton As MSForms.OptionButton
Set myButton = myFrame.Controls.Add("Forms.OptionButton.1")
Dim labelWidth As Double
.Caption = myLabel
.left = 6
.top = 6
.AutoSize = True
labelWidth = .width
.left = 6
.top = 6
.Height = 36
.width = labelWidth + 12
.Caption = "My Frame"
.Properties("Top") = 0
.Properties("Left") = 0
.Properties("Height") = 72.75
.Properties("Width") = 34.5 + labelWidth
.Properties("Caption") = "My Form"
.Properties("Name") = "MyNewForm"
'TODO: Add code for form
MsgBox "Form created"
Debug.Print "Form created"
'This does not work
'This method of adding the form to the UserForms collection
'then showing it does not work either
Set myButton = Nothing
Set myFrame = Nothing
Set myNewForm = Nothing
'Close temporary workbook without saving
Set tempWorkbook = Nothing
In Walkenbach's book he states:
If you'd like to show the form after adding the controls at design time, you need to add the following instruction right before the End Sub statement:
It took me quite a while to figure out how to actually display the UserForm. When VBA generates the 100-button UserForm, it indeed exists in VBA's memory, but it isn't officially part of the project yet. So you need the Add method to formally enroll UserForm1 into the collection of UserForms. The return value of this method is a reference to the form itself, which is why the Show method can be appended to the end of the Add method. So, as a rule, the UserForm must be added to the UserForms collection before it can be used.
I know the workbook and form are being created properly as I can see it while in debug mode and if I comment out the closing of the workbook I can see the form in the Project Explorer.
Any ideas? Thanks!