Excel

Programmatically Add a UserForm

Ease of Use

Easy

Version tested with

2000 

Submitted by:

johnske

Description:

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

Discussion:

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. 

Code:

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 475 times.

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