Run-Time Error 91 When Dynamically Creating Controls and Event Handlers

I'm trying to create a userform and controls at run-time, then add controls in response to user actions. The controls would need event handlers.

For example, the form would have a button (AddEventProcButton) whose event handler (AddEventProcButton_Click) would create another button (TestEventProcButton) and an event handler (TestEventProcButton_Click) for that button.

I have successfully created the form and the first button and its event handler, which calls another subroutine (AddTestButton, that then adds the second button and its event handler). But when I click the first button, I get the following error message:

Run-time error '91':
Object variable or With block variable not set

The interesting thing is I can invoke the AddTestButton subroutine from the initial macro and it works just fine, but invoking it from the event handler (AddEventProcButton_Click) gives the error message. The initial macro and subroutine are in a separate module (Module1) and the event handlers are created in the code module of the new userform.

If that isn't clear enough, I've attached the spreadsheet. You can invoke the macro named DoDataPoints and then click the button on the userform that displays, which will cause the error.

Thanks for any help you can give!

Hello chucklod,

I made the needed changes to code to make it work. You were very close to having it work. I am including the macros here for the other members to see.

Option Explicit

Public TestForm
Public AddEventProcButton
Public TestEventProcButton

Sub DoDataPoints()
'
    Dim N As Long
    Dim LineCount
    Dim MacroString As String

    ' Create the base form
    Set TestForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With TestForm
        .Properties("Top") = 0
        .Properties("Left") = 0
        .Properties("Height") = 85
        .Properties("Width") = 240
        .Properties("Caption") = "Test Form"
        .Properties("StartUpPosition") = 2
    End With
    
    ' Create the button which will add the other button
    Set AddEventProcButton = TestForm.Designer.Controls.Add("forms.CommandButton.1")
    With AddEventProcButton
        .Name = "AddEventProcButton"
        .Caption = "Add Event Proc"
        .Height = 24
        .Width = 66
        .Left = 36
        .Top = 18
    End With

    ' Create the event handlers
     N = TestForm.CodeModule.CreateEventProc("Terminate", "UserForm")
     MacroString = _
        vbCrLf & "    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TestForm"
     TestForm.CodeModule.InsertLines N + 1, MacroString
     
     N = TestForm.CodeModule.CreateEventProc("Click", "AddEventProcButton")
     TestForm.CodeModule.InsertLines N + 1, vbCrLf & "   AddTestButton"
     
     UserForms.Add(TestForm.Name).Show

End Sub ' DoDataPoints


Sub AddTestButton()

    Dim Ctrl As Control
    Dim LineCount
    Dim N As Long
    Dim MacroString As String
    
  Set TestForm = ThisWorkbook.VBProject.VBComponents("UserForm1")
  Load UserForm1
  
  MsgBox "Before 'Set', form name = """ & TestForm.Name & """"
  MsgBox "After 'Set', before 'With'"
  
    Set Ctrl = UserForms(0).Controls.Add("Forms.CommandButton.1", "TestEventProcButton", True)
    With Ctrl
        .Caption = "Test Event Proc"
        .Height = 24
        .Width = 66
        .Left = 126
        .Top = 18
    End With

    MacroString = _
        "Sub TestEventProcButton_Click()" & vbCrLf & _
        "    MsgBox ""This routine was added by code""" & vbCrLf & _
        "End Sub"
        
    With TestForm.CodeModule
      N = .CountOfLines + 1
      .InsertLines N, MacroString
    End With
    
End Sub

Sincerely,
Leith Ross

Thanks. I appreciate your assistance on this. It was driving me nuts.

Two observations from examining your changes:

- I infer from your revisions that the AddFromString property isn't 100% reliable, at least for this type of deferred operation.
- I don't want to hard code the name of the form in the AddTestButton subroutine, so I'll work on that a bit.

There is good news and bad news:

Good news is that now the error 91 is gone. The button added at run-time appears and the event handler is created in the code module of the userform.

Bad news is that clicking the new button does not cause anything to happen, as if the event handler were not connecting to the event. The message box does not display. Repeatedly clicking the button (as in frustration lol) does nothing.

Any suggestions?

Chuck Lodholm

Hello Chuck,

The response of the Forum ISP is driving me crazy. I would have answered you earlier, but the Forum wasn't responding. The general rule I use when programming with the VBIDE is to create the object,and have the system add the event handler to the module. If additional code is needed later, I'll use the AddFromString method.

I will have to get back to you about the why the added button is not functioning when clicked.

Sincerely
Leith Ross

I understand the frustration with the forum response time. Maybe one time in five, I get a message that a database error occurred, the administrator has been notified and hit the refresh button.

I'm trying to figure out other ways to accomplish the task. The driving criterion here is that I don't know how many buttons I will need until the user actually uses the form (what I posted was just a test - the actual form is much more complex). If I were to establish the largest number that would ever be required, I might be able to work with that; but I really would prefer not to code up 100 sets of controls, hide them, and then display them only when needed.

I look forward to seeing what you suggest about connecting the control to its newly created event handler. In the meantime, I can continue development on other fronts and come back to this when we have a working solution.

Thanks for your help,
Chuck L

Hi, Leith,

Have you anything new to add? If you have any ideas but little time to research, just aim me in the direction and I can do the research here.

Thanks for your help,
Chuck L