Create UserForm at Runtime using Code

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

The following code, I found at http://www.eng-tips.com, 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
  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:

Option Explicit

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") = _
        "HSBC Global Resourcing "
    .Properties("Width") = 400
    .Properties("Height") = 300
  End With
  FormName = TempForm.Name
  
'Add a CommandButton
  Set NewButton = TempForm.Designer.Controls _
    .Add("forms.CommandButton.1")
  With NewButton
    .Caption = "Click Me"
    .Width = 60
    .Height = 24
    .Left = TempForm.Properties("InsideWidth") - 2 - 60
    .Top = TempForm.Properties("insideheight") - 2 - 24
  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


VBE Programming – www.vbaexpress.com

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: