Set Click event for command button created with macro

[jc113883]

Hi all,

I am creating a command button object on a userform with code during run time. What is the best method for specifying the code that will run when the button is clicked?

Graeme.

 

[Derek Brown]

I don't understand the problem - if you double-click the button on the form in design mode, you can then enter the code that runs 'on click'?

 

[Andrew Poulsom]

You can use the CreateEventProc method of the CodeModule object to create an event procedure. See:

http://www.cpearson.com/excel/vbe.htm

 

[jc113883]

Thanks Andrew. That is a very useful web page... something I've been looking for.

Unfortunately though, that method did not work. The button that I create is created during run time and I want to assign code for it at the same time. Using the CreateEventProc method on the click event fails as the button was not part of the userform when it was loaded and so I get a creat event procedure is invalid error. Furthermore, when I close a userform, any objects I created during run-time disappear.

Another example of where it won't work in run time is as follows. I have a userform with two commandbuttons. The code for the first button creates the Code for the second button. However, this new code for the second button will not work right away. The userform has to be re-loaded.

Further suggestions are appreciated.