Programmatically Add a UserForm

Ease of Use


Version tested with


Submitted by:



Create a userform with a combo box and the command buttons "OK" and "Cancel" will be created and shown 


You may wish to add a userform or userforms to the project only if (say) certain conditions have been met or satisfied. This demonstrates how to go about it. 


instructions for use


Option Explicit Sub MakeUserForm() Dim MyUserForm As VBComponent Dim NewOptionButton As Msforms.OptionButton Dim NewCommandButton1 As Msforms.CommandButton Dim NewCommandButton2 As Msforms.CommandButton Dim MyComboBox As Msforms.ComboBox Dim N, X As Integer, MaxWidth As Long '//First, check the form doesn't already exist For N = 1 To ActiveWorkbook.VBProject.VBComponents.Count If ActiveWorkbook.VBProject.VBComponents(N).Name = "NewForm" Then ShowForm Exit Sub Else End If Next N '//Make a userform Set MyUserForm = ActiveWorkbook.VBProject _ .VBComponents.Add(vbext_ct_MSForm) With MyUserForm .Properties("Height") = 100 .Properties("Width") = 200 On Error Resume Next .Name = "NewForm" .Properties("Caption") = "Here is your user form" End With '//Add a Cancel button to the form Set NewCommandButton1 = MyUserForm.Designer.Controls.Add("forms.CommandButton.1") With NewCommandButton1 .Caption = "Cancel" .Height = 18 .Width = 44 .Left = MaxWidth + 147 .Top = 6 End With '//Add an OK button to the form Set NewCommandButton2 = MyUserForm.Designer.Controls.Add("forms.CommandButton.1") With NewCommandButton2 .Caption = "OK" .Height = 18 .Width = 44 .Left = MaxWidth + 147 .Top = 28 End With '//Add code on the form for the CommandButtons With MyUserForm.CodeModule X = .CountOfLines .InsertLines X + 1, "Sub CommandButton1_Click()" .InsertLines X + 2, " Unload Me" .InsertLines X + 3, "End Sub" .InsertLines X + 4, "" .InsertLines X + 5, "Sub CommandButton2_Click()" .InsertLines X + 6, " Unload Me" .InsertLines X + 7, "End Sub" End With '//Add a combo box on the form Set MyComboBox = MyUserForm.Designer.Controls.Add("Forms.ComboBox.1") With MyComboBox .Name = "Combo1" .Left = 10 .Top = 10 .Height = 16 .Width = 100 End With ShowForm End Sub Sub ShowForm() NewForm.Show End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code into the Module
  5. Now select Tools/References, scroll down and...
  6. Tick 'Microsoft Visual Basic for Applications Extensibility 5.3'
  7. Now select File/Close and Return To Microsoft Excel

Test the code:

  1. Go into the VBE window and check that there is no existing UserForm
  2. Go back to the main window, select Tools/Macro/Macros.../MakeUserForm/Run
  3. (If you receive error messages make sure you have ticked the following...
  4. Microsoft Visual Basic for Applications Extensibility 5.3; Microsoft Forms 2.0 Object Library)

Sample File:

MakeUserForm.zip 8.4KB 

Approved by mdmackillop

This entry has been viewed 473 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express