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.

Please Login or Register  to view this content.

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

Hello Chuck,

After a lot of testing, I am no more closer to having an answer as to why this doesn't work than when we started. I know this is doable in VB, but I am beginning to suspect otherwise when doing it in VBA. Sorry the news isn't more positive.

Sincerely,
Leith Ross

I appreciate your effort. I have seen a lot of mention on numerous web sites and books about the ability to do this kind of thing, but nobody addresses the issue of tying a dynamically-created control to its dynamically-created event handler. Like you, I pause to consider: maybe it's not possible under the current VBA state of the art.

I've had Walkenbach's books for year and even he doesn't quite hit this point. Verrrry interrresting.

Chuck L

Solution:
After much wrestling and experimenting, I've found a solution that works.

The solution involves class module programming. It sets up a class of object handling for command buttons (and other controls, by the way) and identifies exactly which buttons are to be handled. Those which are not handled by the class, are then handled by an associated event handler, if any, defined in the form's code sheet.

What this code does, then, is to use a predefined userform with a single command button, which calls code that adds another button at run-time. The problem has always been connecting the new button to its new event handler, both created at run-time. My online research has not discovered a solution for that problem, which of course, doesn't mean there is none, but it forced me to look in another direction, which I described in the prior paragraph. The spreadsheet with code is attached for your perusal.

One should be able to create separate classes for separate control types and within a single control type, handle things differently based on control name or caption. This is extremely useful for situations where you have large numbers of controls and don't want to write an event handler for each, as well as this, where controls and their event handlers are being created at run-time.

References:
- Excel 2003 Power Programming with VBA by John Walkenbach, 2004, Wiley Publishing, Indianapolis, Indiana. This book can teach just about anyone to program in VBA, with lots of rich examples and a CD so you don't have to type the examples in. Supported by his website: http://www.j-walk.com/ss/

- http://www.jkp-ads.com/Articles/ControlHandler00.htm An excellent site based in the UK or Netherlands, which provided the examples on which I based my solution.

- http://www.vbusers.com/ has many useful examples. I used Download 13 "Make a VBA control array (both on a form and worksheet)" to assist in this solution.

- http://www.cpearson.com/excel/mainpage.aspx has a number of helpful routines, some of which relate to this task.