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!
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Metal 3D printing has rapidly emerged as a key technology in modern design and manufacturing, so itâ€™s critical educational institutions include it in their curricula to avoid leaving students at a disadvantage as they enter the workforce. Download Now
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now