Programatically Add Controls to a UserForm

Ease of Use


Version tested with


Submitted by:

Jacob Hilderbrand


This macro demonstrates how to use VBA to add controls to a UserForm. 


You want to add a specific control (i.e. Command Button) to several UserForms. You want all the Command Buttons to have the same caption, and click macro, but you don't want to add all the Command Buttons Manually. This macro demonstrates how to get VBA to work on a UserForm. 


instructions for use


Option Explicit Sub AddControls() Dim Frm As Object Dim Btn As MSForms.CommandButton Dim x As Long Dim n As Long Dim BtnName As String For x = 1 To ThisWorkbook.VBProject.VBComponents.Count If ThisWorkbook.VBProject.VBComponents(x).Type = 3 Then Set Frm = ThisWorkbook.VBProject.VBComponents(x) Set Btn = Frm.Designer.Controls.Add("forms.CommandButton.1") With Btn .Caption = "Caption" .Height = 25 .Width = 60 .Left = 12 .Top = 6 End With With ThisWorkbook.VBProject.VBComponents(x).CodeModule n = .CountOfLines .InsertLines n + 1, "Sub CommandButton1_Click()" .InsertLines n + 2, vbNewLine .InsertLines n + 3, vbTab & "MsgBox " & """" & "Hi" & """" .InsertLines n + 4, vbNewLine .InsertLines n + 5, "End Sub" End With End If Next x End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code in the Code Window that opens up.
  5. Insert several UserForms (Insert | UserForm).
  6. Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

  1. Make sure that "Trust access to VBA Project" is enabled (Tools | Macro | Security | Trusted Sources).
  2. Tools | Macro | Macros...
  3. Select AddControls and press Run.
  4. Open the VBE and check your UserForms.

Sample File:

No Attachment 

Approved by Anne Troy

This entry has been viewed 401 times.

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