Code to create UserForm


Well-known Member
Jan 17, 2006
Office Version
  1. 365
  1. Windows
I am looking to to write some code that will programatically create a UserForm based on a series of selections I enter onto a worksheet.

I am aware that this is possible but I can't find any code that tells me how to do this. I did find something on the Microsoft site but it related to a an old version of Office and didn't work.

Can anyone point me in the right direction?


Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Here's an example I found on John Walkenbach's website. There's even an example file.

Excel Developer Tip: Creating a UserForm Programmatically

I referenced Microsoft Forms 2.0 and then did this:

1. Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click Application Options.
2. Click the Trust Center tab, and then click Trust Center Settings.
3. Click the Macro Settings tab, click to select the Trust access to the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; background-color: rgb(250, 250, 250);">VBA</acronym> project object model check box, and then click OK.
4. Click OK

Attribute VB_Name = "modGetOption"
Option Explicit
'Passed back to the function from the UserForm

Function GetOption(OpArray, Default, Title)
    Dim TempForm  'As VBComponent
    Dim NewOptionButton As MSForms.OptionButton
    Dim NewCommandButton1 As MSForms.CommandButton
    Dim NewCommandButton2 As MSForms.CommandButton
    Dim TextLocation As Integer
    Dim X As Integer, i As Integer, TopPos As Integer
    Dim MaxWidth As Long
    Dim WasVisible As Boolean
'   Hide VBE window to prevent screen flashing
    Application.VBE.MainWindow.Visible = False

'   Create the UserForm
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
    TempForm.Properties("Width") = 800
'   Add the OptionButtons
    TopPos = 4
    MaxWidth = 0 'Stores width of widest OptionButton
    For i = LBound(OpArray) To UBound(OpArray)
        Set NewOptionButton = TempForm.Designer.Controls.Add("forms.OptionButton.1")
        With NewOptionButton
            .Width = 800
            .Caption = OpArray(i)
            .Height = 15
            .Left = 8
            .Top = TopPos
            .Tag = i
            .AutoSize = True
            If Default = i Then .Value = True
            If .Width > MaxWidth Then MaxWidth = .Width
        End With
        TopPos = TopPos + 15
    Next i
'   Add the Cancel button
    Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewCommandButton1
        .Caption = "Cancel"
        .Height = 18
        .Width = 44
        .Left = MaxWidth + 12
        .Top = 6
    End With

'   Add the OK button
    Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewCommandButton2
        .Caption = "OK"
        .Height = 18
        .Width = 44
        .Left = MaxWidth + 12
        .Top = 28
    End With

'   Add event-hander subs for the CommandButtons
    With TempForm.CodeModule
        X = .CountOfLines
        .InsertLines X + 1, "Sub CommandButton1_Click()"
        .InsertLines X + 2, "  GETOPTION_RET_VAL=False"
        .InsertLines X + 3, "  Unload Me"
        .InsertLines X + 4, "End Sub"
        .InsertLines X + 5, "Sub CommandButton2_Click()"
        .InsertLines X + 6, "  Dim ctl"
        .InsertLines X + 7, "  GETOPTION_RET_VAL = False"
        .InsertLines X + 8, "  For Each ctl In Me.Controls"
        .InsertLines X + 9, "    If ctl.Tag <> """" Then If ctl Then GETOPTION_RET_VAL = ctl.Tag"
        .InsertLines X + 10, "  Next ctl"
        .InsertLines X + 11, "  Unload Me"
        .InsertLines X + 12, "End Sub"
    End With
'   Adjust the form
    With TempForm
        .Properties("Caption") = Title
        .Properties("Width") = NewCommandButton1.Left + NewCommandButton1.Width + 10
        If .Properties("Width") < 160 Then
            .Properties("Width") = 160
            NewCommandButton1.Left = 106
            NewCommandButton2.Left = 106
        End If
        .Properties("Height") = TopPos + 24
    End With

'   Show the form

'   Delete the form
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
'   Pass the selected option back to the calling procedure
End Function

Sub DemoGetOption()
    Dim Ops(1 To 12) As String
    Dim i As Integer
    Dim UserChoice As Variant
'   Create an array of month names
    For i = 1 To 12
        Ops(i) = Format(DateSerial(1, i, 1), "mmmm")
    Next i
    UserChoice = GetOption(Ops, 1, "Select a month")
    If UserChoice <> False Then MsgBox UserChoice
End Sub
Last edited:
Upvote 0
One thing I am interested in doing is selecting some of the controls, for example a label and a combobox, and the aligning by the 'Middles' selection.

By using the .Top command this would do the job but it's not quite exactly aligned. Being a bit anal about these things I'd like to perform it via code if possible.
Upvote 0
I'll have a look through that and see if it helps.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back