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:
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!
Talk To Other Members