Excel

Programatically Add Controls to a UserForm

Ease of Use

Hard

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

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

Discussion:

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. 

Code:

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

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