Building a new Excel UserForm on the fly, having issues

I'm using the code in this link to create a UserForm on the fly:

But I'm getting a "Variable Not Defined" compile error on the following line:

[CODE]Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)[/CODE]

When debugging, it is highlighting the "vbext_ct_MSForm" part of the line, the argument for the "Add" method. Any ideas?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Rory ArchibaldConnect With a Mentor Commented:
Sure. Essentially, when you declare a variable WithEvents, you tell VB that you want the variable to respond to the events exposed by that variable's type. You then write the event code for the variable even though it does not actually exist as an object yet.
When the form is loaded and you add controls to it, you assign the relevant control to the relevant variable, and the events are automatically connected to that control (what's known as an event sink).
I've attached a very quick demo workbook. You will note that there are no controls on the form until it's loaded, but the controls still respond to being clicked.
Any questions, please shout!
Have you set the reference to the 'Microsoft Visual Basic for Applications Extensibility' library?
PatrickJohnsonAuthor Commented:
Duh! I had, but I had since copied all my code into a new workbook because the other got corrupted. I must not have set the reference in the new one. I'll check it and report back ASAP.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

PatrickJohnsonAuthor Commented:
Okay, that fixed that issue, and the form is building just fine. However, I can't get any event code to run when I change values on the form. It seems as though once it builds the form and shows it, VBA pauses and never exits the sub. Would a DoEvents at the end of the code referenced above help?

I've checked to ensure that my click event code names match the control name they are attached to, and all the code has in it right now is a msgbox for testing to ensure it is running (which it isn't).

It is almost as though the form is modal and won't let anything proceed. Could that be it?
PatrickJohnsonAuthor Commented:
I've gotten the code to proceed by changing the last line to:

    VBA.UserForms.Add(FormName).Show (vbModeless)

However, the form disappears at the end of the routine?!?!

At first I thought it was because the original code has a statement to delete the form at the end, but I took that out with the same result. Thoughts?
Did you try the sample code in this thread?  It shows how to create events for dynamicallty created forms (Excel version is a little old but it might point you in the direction)
PatrickJohnsonAuthor Commented:
That is the exact code I'm using. the problem isn't with the creation of the events code, that works flawlessly.

The problem is that the form never shows. Before I added the "vbModeless" argument to the Show statement, the form would pop up modally, and because it was modal the events wouldn't trigger because the procedure which created the form was still running.

I would have thought that changing the form to show modelessly would have sufficed, but now it just doesn't pop up at all. The form is created just fine, as I can see it in the project browser, and all the associated code I built on the fly is intact. The only problem is the darn thing won't stay open!
Rory ArchibaldCommented:
You can't be using that *exact* code since there are no values to change on the form built by that code. What exact code are you using?
PatrickJohnsonAuthor Commented:
Fair enough. I'll post the actual code I'm using below. Let me also explain what I'm trying to do.

My company runs 11 payrolls. There are a number of specific steps that need to be completed for each payroll run, and the steps are specific to each payroll in many cases. So, my goal is to build an excel file which on opening shows a userform that allows a user to choose a payroll name and a period end date (these have to be completed each payroll run), and it will then build another userform on the fly which lists each step and it's status, indicated by option buttons.

Each step has an option button marked "Incomplete" and another marked "Complete" and each pair is within a frame labeled by step name. Some also have a third option, "None Received", which is when someone in the field has not sent any files to be processed for that step.

The steps are all in one large list on a sheet called "Steps" for all payrolls. Once the form building routine is called, it filters the list down to the specified pay group and copies the resulting list to the "ListLookups" tab, which is where the data will be accessed mostly during building the form.

If it's the first time that period and payroll have been selected (indicated by a flag on the "SystemVariables" sheet, all steps have the "Incomplete" option button marked. If the payroll has been started already, the form building routine will check another sheet ("Status") which is populated with the status of each step and populate the new form accordingly.

The form should then display all the steps and their status, and allow the user to change the status as they complete the steps. They can close at any time, and the step status indicators are to be saved on the "Status" page so that the form can be updated when they open it back up.

The code I want to put on the option buttons should do the following (in pseudo code):

If "Complete" or "None Received" Then
Grab logged in username
Grab current date and time
Concatenate the two into a string
store the string in a label next to the step
clear status label

I should note that the code works in it's entirety, and as long as I load the newly-built form modally, everything works as it should EXCEPT that because the form is modal code execution is paused while it is open, so the user can't interact with the form. The option buttons can be changed, but it triggers no events.

If I load the form modelessly, the code completes, but the form doesn't remain open after it is built and loaded. There is where the problem lies. I know the form and code are being created, because I can open the form manually after the routine finishes.

Sub MakeForm()
    Dim TempForm As Object ' VBComponent
    Dim FormName As String ' Name of the form to be created
    Dim Option1 As MSForms.OptionButton ' "Incomplete" option button for each step
    Dim Option2 As MSForms.OptionButton ' "Complete" option button for each step
    Dim Option3 As MSForms.OptionButton ' "None Received" option button for selected steps
    Dim Label1 As MSForms.Label ' label control that will contain the text for each step
    Dim Label2 As MSForms.Label ' label control that will contain username and datetime info for completed steps
    Dim Frame1 As MSForms.Frame
    Dim stepsheet As Worksheet ' sheet containing the step list being evaluated
    Dim DataRow As Long, EndRow As Long ' row boundaries for cycling through the list of payroll steps to populate the form
    Dim c As Range ' range variable
    Dim FrameCount As Integer
    Dim lngHeight As Long
    Dim StatusRow As Long
'   ** Additional variable
    Dim CodeLines As Integer ' number of lines of code in the form module
    'assign step list sheet to 'stepsheet' variable
    Set stepsheet = ThisWorkbook.Worksheets("Steps")

    FrameCount = 0

    With stepsheet
        'Filter list pay group specified in frmStartData combo box
        '(data was first moved to a sheet in the workbook when the form was unloaded)
        EndRow = .Range("A65536").End(xlUp).Row
        .AutoFilterMode = False
        .Range("A:F").AutoFilter 1, ThisWorkbook.Sheets("SystemVariables").Range("B7").Value

        'Copy filtered step list
        .Range("A1:F" & EndRow).SpecialCells(xlCellTypeVisible).Copy
    End With 'for "with stepsheet" statement
    'paste filtered list into LookupLists worksheet
    'If this is the first time this particular checklist has been opened
    'set up cells to house status of each payroll step
    If ThisWorkbook.Sheets("SystemVariables").Range("B1").Value = "No" Then
        ThisWorkbook.Sheets("Status").Range("D1").Value = "Status"
        ThisWorkbook.Sheets("Status").Range("E1").Value = "DateTime"
        ThisWorkbook.Sheets("Status").Range("F1").Value = "User"
    End If ' for 'If ThisWorkbook.Sheets("SystemVariables").Range("B1").Value = "No" Then' Statement
    'Find last row of list of steps
    EndRow = ThisWorkbook.Sheets("LookupLists").Range("C65536").End(xlUp).Row

    'set stepsheet to "LookupLists" sheet to begin cycling through the step list
    Set stepsheet = ThisWorkbook.Sheets("LookupLists")

    '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") = ThisWorkbook.Sheets("SystemVariables").Range("B7").Value & " Payroll Processing - Period End " & ThisWorkbook.Sheets("SystemVariables").Range("B4").Value
        .Properties("Height") = 100
        .Properties("Height") = 400
    End With

    FormName = TempForm.Name
    'loop through steps and build form
    For DataRow = 3 To EndRow
        '---BUILD FRAME---'

        'Add a Frame for each step
        Set Frame1 = TempForm.Designer.Controls.Add("Forms.Frame.1", "Frame" & FrameCount + 1, True)
        'set properties of frame
        With Frame1
            .Width = 640
            .Top = stepsheet.Range("D" & DataRow).Value * 25
            Debug.Print Frame1.Top
            .Left = 10
            .Height = 25
        End With ' For 'With Frame1' statement
        Debug.Print Frame1.Left + Frame1.Width
        Debug.Print TempForm.Properties("Width").Value
        If Frame1.Left + Frame1.Width > TempForm.Properties("Width").Value Then
            TempForm.Properties("Width") = Frame1.Left + Frame1.Width
        End If
        'write event handler code for the frame
        With TempForm.CodeModule
                CodeLines = .CountOfLines
                .InsertLines CodeLines + 1, "Private Sub Frame" & FrameCount + 1 & "_Change()"
                .InsertLines CodeLines + 2, "MsgBox " & Chr(34) & "Change Working" & Chr(34)
                .InsertLines CodeLines + 3, "Me.lblStatus" & stepsheet.Range("D" & DataRow).Value & ".Caption = Nothing"
                .InsertLines CodeLines + 4, "End Sub"
        End With ' for 'With TempForm.CodeModule' statement
        'set height of form to accomodate new frame
        lngHeight = Frame1.Top + Frame1.Height
        'Add a label into the frame and populate the caption with the name of the step
        Set Label1 = Frame1.Controls.Add("Forms.Label.1", "lbl" & stepsheet.Range("D" & DataRow).Value, True)
        'set label properties
        With Label1
            .Top = .Top + 5
            .Left = Frame1.Left
            .Height = Frame1.Height
            .Width = 385
            .Caption = stepsheet.Range("E" & DataRow).Value
        End With ' for 'With Label1' statement

        'If the step being evaluated is a grouping, hide frame borders
        If stepsheet.Range("G" & DataRow).Value = "" And stepsheet.Range("F" & DataRow).Value = "" And stepsheet.Range("H" & DataRow).Value = "" Then
            Frame1.BorderStyle = fmBorderStyleNone
            Frame1.SpecialEffect = fmSpecialEffectFlat
            Label1.Font.Bold = True
            Label1.Font.Size = 11
        Else ' for 'If stepsheet.Range("G" & DataRow).Value = "" And stepsheet.Range("F" & DataRow).Value = "" And stepsheet.Range("H" & DataRow).Value = "" Then' statement
            '---BUILD OPTION BUTTONS---'
            'Populate frame with option buttons where indicated in the step list
            '---BUILD OPTION1 BUTTON ("Incomplete" Status Button)---'
            'Check for "Incomplete" option
            Set c = stepsheet.Range("F" & DataRow)
            If c.Value <> "" Then
                Set Option1 = Frame1.Controls.Add("Forms.OptionButton.1", "opta" & stepsheet.Range("D" & DataRow).Value, True)
                'set option button properties
                With Option1
                    .Height = Label1.Height
                    .Width = 75
                    .Left = Label1.Left + Label1.Width
                    .Caption = c.Value
                    .Value = True
                End With ' for 'With Option1' statement
            End If ' for 'If c.Value <> "" Then' Statement
            '---BUILD OPTION2 BUTTON ("Complete" Status Button)---'
            'Check for "Complete" option
            Set c = stepsheet.Range("G" & DataRow)
            If c.Value <> "" Then
                Set Option2 = Frame1.Controls.Add("Forms.OptionButton.1", "optb" & stepsheet.Range("D" & DataRow).Value, True)

                'set option button properties
                With Option2
                    .Height = Option1.Height
                    .Width = Option1.Width
                    .Top = Option1.Top
                    .Left = Option1.Left + Option1.Width
                    .Caption = c.Value
                End With ' for 'With Option2' statement
            End If ' for 'If c.Value <> "" Then' Statement
            '---BUILD OPTION3 BUTTON ("None Received" Status Button)---'
            Set c = stepsheet.Range("H" & DataRow)
            If c.Value <> "" Then
                Set Option3 = Frame1.Controls.Add("Forms.OptionButton.1", "optc" & stepsheet.Range("D" & DataRow).Value, True)

                'set option button properties
                With Option3
                    .Height = Option1.Height
                    .Width = Option1.Width
                    .Top = Option1.Top
                    .Left = Option2.Left + Option2.Width
                    .Caption = c.Value
                End With ' for 'With Option3' statement
            End If ' for 'If c.Value <> "" Then' Statement
            'Build Label to house information regarding status details
            Set Label2 = TempForm.Designer.Controls.Add("Forms.Label.1", "lblStatus" & stepsheet.Range("D" & DataRow).Value, True)

                'set label properties
                With Label2
                    .Caption = "*"
                    .Height = Frame1.Height
                    .Left = Frame1.Left + Frame1.Width
                    .Width = 160
                    .Top = Frame1.Top + 10
                    .Font.Bold = True
                End With ' for 'With Label2' statement
                If Label2.Left + Label2.Width > TempForm.Properties("Width") Then
                    TempForm.Properties("Width") = Label2.Left + Label2.Width
                End If
    End If ' for 'If stepsheet.Range("G" & DataRow).Value = "" And stepsheet.Range("F" & DataRow).Value = "" And stepsheet.Range("H" & DataRow).Value = "" Then' statement
        'if this is a checklist already in progress, retrieve status of each step
        If ThisWorkbook.Sheets("SystemVariables").Range("B1").Value = "Yes" Then

            'find step name and use its row number to look up status of step
            If pFindRowPos(ThisWorkbook.Sheets("Status"), Label1.Caption, xlNext, xlByRows) > 0 Then

                StatusRow = pFindRowPos(ThisWorkbook.Sheets("Status"), Label1.Caption, xlNext, xlByRows)

                'set option button values and label2 contents based on the step's current status
                Select Case ThisWorkbook.Sheets("Status").Range("D" & StatusRow).Value

                    Case "Complete"
                        Option2.Value = True
                        Label2.Caption = ThisWorkbook.Sheets("Status").Range("F" & StatusRow).Value & " - " & ThisWorkbook.Sheets("Status").Range("E" & StatusRow).Value
                    Case "Incomplete"
                        Option1.Value = True
                    Case "None Received"
                        Option3.Value = True
                        Label2.Caption = ThisWorkbook.Sheets("Status").Range("F" & StatusRow).Value & " - " & ThisWorkbook.Sheets("Status").Range("E" & StatusRow).Value

                End Select ' for 'Select Case ThisWorkbook.Sheets("Status").Range("D" & StatusRow).Value' statement

            End If ' for 'If pFindRowPos(ThisWorkbook.Sheets("Status"), Label1.Caption, xlNext, xlByRows) > 0 Then' statement

        End If ' for 'If ThisWorkbook.Sheets("SystemVariables").Range("B1").Value = "Yes" Then' statement

        'increment FrameCount
        FrameCount = FrameCount + 1
    Next DataRow ' for 'For DataRow = 3 To EndRow' statement
'add/set scroll bar properties based on height of completed form
With TempForm.Designer

    Debug.Print "Height = " & lngHeight
    If lngHeight > 400 Then
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = .InsideHeight * (lngHeight / 375)
        .ScrollWidth = .InsideWidth * 9
    End If ' for 'If lngHeight > 400 Then' Statement

End With ' for 'with tempform' statement

'   Show the form
TempForm.Properties("ShowModal") = False
Unload frmWait
VBA.UserForms.Add (FormName)
'Delete commented out
'   Delete the form
'    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
End Sub

Open in new window

Rory ArchibaldConnect With a Mentor Commented:
Your code doesn't appear to be showing the form at all, and you appear to be trying to use a Change event of the Frame controls but there is no such event.
If I were you I would use a blank form and simply add controls to it at runtime - it's a lot easier than creating the whole thing from scratch.
PatrickJohnsonAuthor Commented:
Ah, sorry. I had it showing the form at the end before, then I moved the form show to an external routine called "LoadChecklist" referenced at the end of the code. I'll alter it to show an existing form and add the controls and such as opposed to adding the form as well and report back.
PatrickJohnsonAuthor Commented:
Okay, I've pre-made the form, and altered the code to sit in the Initialize event of the form. This way, the form is already there, it will just be altered by the code. However, now I get an automation error (Object invoked has disconnected from its clients) at the portion that adds the event handler code. Thoughts?

FYI, If I remove the code writing portion, everything works fine.

Here is the code:

For Each ctl In Me.Controls
    Debug.Print TypeName(ctl)
    If TypeName(ctl) = "OptionButton" Then
        With ThisWorkbook.VBProject.VBComponents("frmChecklist").CodeModule
                CodeLines = .CountOfLines
                .InsertLines CodeLines + 1, "Private Sub " & ctl.Name & "_Click()"
                .InsertLines CodeLines + 2, "MsgBox " & Chr(34) & "Change Working" & Chr(34)
                If ctl.Caption = "Incomplete" Then
                    .InsertLines CodeLines + 3, "Me.lblStatus" & stepsheet.Range("D" & DataRow).Value & ".Caption = Nothing"
                    .InsertLines CodeLines + 3, "Me.lblStatus" & stepsheet.Range("D" & DataRow).Value & ".Caption = Environ(" & Chr(34) & "USERNAME" & Chr(34) & ") & " & Chr(34) & " - " & Chr(34) & " & Now"
                End If
                .InsertLines CodeLines + 4, "End Sub"
        End With ' for 'With TempForm.CodeModule' statement
    End If
Next ctl

Open in new window

Rory ArchibaldCommented:
I would advise two options:
1. Use variables declared WithEvents to respond to the events you need, rather than writing event code at runtime;
2. Modify your form from a calling routine before showing it, rather than using the Form's initialize event (so you are not trying to rebuild the form's code whilst running code in the form)
PatrickJohnsonAuthor Commented:
I follow you on Number 2, but I've never really used WithEvents, could you give me a little guidance?
PatrickJohnsonAuthor Commented:
Okay, I think I get the concept, but I have a question. If I'm assigning every control to this one variable, how is that possible? Can't I only assign one control at a time to the variable? Maybe I'm not understanding it as much as I think I am...

It appears as though you are defining a global variable which each new control is assigned to when creating the control. If that's so, wouldn't the last control made be the only one which the event would be recognized for?
PatrickJohnsonAuthor Commented:
In some further searching on the subject, I found this little example which may be the answer:

It uses the same method of WithEvents you are using, but declares the variable used as an array so that all the controls of the type can be stored in it. I'll give it a shot and report back.

Rory ArchibaldCommented:
In that example, you can only have one control per variable. If you need more, and the code for each is similar (e.g. you have groups of checkboxes) then you can create a class module, declare the withevents variable(s) in the class and then in the form you need a Collection object to which you assign multiple instances of the class.
Based on what I understand of your process, you would probably want a class that declares a frame variable and three option buttons and then has event code for each control. Your form code would then loop through your steps, create an instance of the class for each step, add a frame and two or three option buttons to the form, assign those to the variables for the class, then add the class instance to the collection.

Since I can't be more specific than that, you might find it easier to go with the second option I suggested! Or, if you can post a sample workbook, I can try and elaborate as and when I have the time.

PatrickJohnsonAuthor Commented:
Using a combination of your solution and the link I posted, I've got it working flawlessly! Thanks so much for the help!
PatrickJohnsonAuthor Commented:
Got it working! Answer accepted, points awarded. Thanks!
All Courses

From novice to tech pro — start learning today.