Well, this is something that I came across recently, and it was a pain to find. So, in the hopes that I can save someone else a headache, here is the process to create a UserForm dynamically using a macro in VBA.
1. From the VBE (open Excel and hit ALT+F11)you need to set
a reference to the extensibility add-in. To do this, go
to Tools/References and find the add-in Micrsoft Visual
Basic for Applications Extensibility X.X (mine was 5.3
when I wrote this)
2. Create a macro module in the project file. To do this,
right-click on VBAProject and select Insert->Module.
3. Right-click the module and select View Code.
4. Paste this code into the window:
Code:
Sub MakeForm()
Dim TempForm As Object ' VBComponent
Dim FormName As String
Dim NewButton As MSForms.CommandButton
Dim TextLocation As Integer
' ** Additional variable
Dim X As Integer
'Locks Excel spreadsheet and speeds up form processing
Application.VBE.MainWindow.Visible = False
Application.ScreenUpdating = False
' Create the UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
'Set Properties for TempForm
With TempForm
.Properties("Caption") = "Temporary Form"
.Properties("Width") = 200
.Properties("Height") = 100
End With
FormName = TempForm.Name
' 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
' ** Delete This: TextLocation = .CreateEventProc("Click","CommandButton1")
' ** Add/change next 5 lines
' This code adds the commands/event handlers to the form
X = .CountOfLines
.InsertLines X + 1, "Sub CommandButton1_Click()"
.InsertLines X + 2, "MsgBox ""Hello!"""
.InsertLines X + 3, "Unload Me"
.InsertLines X + 4, "End Sub"
End With
' Show the form
VBA.UserForms.Add(FormName).Show
'
' Delete the form
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
End Sub
You can edit the code as needed to produce what you need. But be sure to include the code to handle whatever objects (comboboxes, textboxes, etc.) you add in. Thanks to John Walkenbach for the code!
