Code to create UserForm

[mikeymay]

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

 

[mrmmickle1]

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
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

 

[mikeymay]

That looks like it will do the job.

Many thanks!

 

[mrmmickle1]

No Problem. I learned something in the process as well!

 

[mikeymay]

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.

 

[mrmmickle1]

This thread may help.

VBA Geek helped me out with some of this dynamic label and textbox stuff a little bit ago. I would do a little trial and error and go from there...

http://www.mrexcel.com/forum/excel-questions/843648-dynamic-userform-textboxes.html

 

[mikeymay]

I'll have a look through that and see if it helps.

Thanks