×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UserForms

How do I create a UserForm in Excel VBA using code? by logius
Posted: 5 Sep 01

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!

Back to Microsoft VBA - Visual Basic for Applications FAQ Index
Back to Microsoft VBA - Visual Basic for Applications Forum

My Archive


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
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
Design for Additive Manufacturing (DfAM)
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
Taking Control of Engineering Documents
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

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close