Code to create UserForm

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,480
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?


Thanks
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
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

Code:
Attribute VB_Name = "modGetOption"
Option Explicit
'Passed back to the function from the UserForm
Public GETOPTION_RET_VAL As Variant


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
    VBA.UserForms.Add(TempForm.Name).Show


'   Delete the form
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
    
'   Pass the selected option back to the calling procedure
    GetOption = GETOPTION_RET_VAL
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:

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,480

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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 MrExcel.com.
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 "mrexcel.com".
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
Top