This post is related to my previous post Changing UserForm Button colour on mouse hover – it's not essential, but you might want to read that post first.
Sometimes you might need to add controls (in this post we use Buttons but it could be CheckBoxes, OptionButtons etc) dynamically to a UserForm. Often this is because you don't know how many Buttons you will need at design time, or you just might not want to have to go through the tedious process of adding lots and lots of Buttons and would prefer doing it in code.
The process of adding Buttons is simple, for example, add a UserForm called UserForm1 (to follow along with this post, I suggest you leave it as UserForm1 for now) and set its width to 198 and height to 184, then add the following code in the UserForm's code-behind:
This adds 26 CommandButtons each displaying a different letter from A to Z, arranged in 4 rows of 6 and 1 row of 2.
Simple. But boring … it does nothing … so lets make it do something.
If you are displaying the UserForm then use the red 'X' to close it then add a Class module (must be a Class module … not a Standard module) and rename it to CCmdBtnEvents (the full code for the Class module is below).
Add a module-level WithEvents variable with a type of MSForms.CommandButton (this is mCmdBtn in the code below). 'WithEvents' is the magic that lets us handle events for all of the Buttons on the UserForm.
Add a module-level variable with a type of the UserForm that you added (this is mUserForm in the code below). Our UserForm is called UserForm1 so that is the type. In our code, this is only used so that we can call back to code in the UserForm and this variable isn't essential for reacting to events for dynamically added controls depending on what you want your code to do.
Then we need a procedure in the Class module that is called from our UserForm_Activate procedure to set the CommandButton and UserForm module-level variables (this is SetUpCommandButton in the code below).
Next, as the whole point is to listen to Button events, we need event handlers. In this example we're going to add two of them … one for Click events and one for MouseMove events. Normally, you should add these using the drop-downs at the top-left and top-right of the code window … but, in this case, you can copy the code below.
Your Class module should then contain this code:
Next, go back to the UserForm code-behind. Again all of the code for the next few steps is displayed below.
Add a module-level dynamic array of CCmdBtnEvents (ie objects of the Class module you just added). The array must be module-level. If you move the declaration of the array inside UserForm_Activate then it (the array) will go 'out of scope' as soon as code execution leaves UserForm_Activate and the Buttons will not respond to events. By declaring the array at the module-level, the array will only go 'out of scope' when the UserForm instance is destroyed which is what we want … i.e. for the events to keep firing until the UserForm is closed.
Add a line at the start of UserForm_Activate to ReDim that array to contain 26 elements (with a lower bound of 1).
Add lines at the end of UserForm_Activate (but within the For … Next loop) to set the element within the array to be a new instance of the CCmdBtnEvents Class and then we pass, into the SetUpCommandButton procedure of that instance, the Button and the UserForm. What we've done here is that we've created an instance of the CCmdBtnEvents Class for each Button, passed the Button (and the UserForm) to that instance of the Class which (in the SetUpCommandButton procedure) has assigned the Button to the mCmdBtn module-level variable that is 'WithEvents' ie we want to have events raised for it. We receive those events in the event handlers we added to the CCmdBtnEvents Class. We added event handlers for Click and MouseMove but you can add others (but see Notes below).
Your UserForm code-behind should now look like this:
Before we go back to the Class module and replace the comment in the Click event handler with code, we need to add (still in the code-behind of the UserForm) the code that the Click event handler will call i.e. what we want to happen when a Button is clicked … add this code:
Simple. Hide the UserForm, show the user which Button was clicked.
The last code to add to the UserForm code-behind will be familiar if you read the Changing UserForm Button colour on mouse hover post first. Because there is an explanation of this code there, I'm not going into detail here other than to say that this resets the background colour of the Buttons back to their default (while the MouseMove event handler in CCmdBtnEvents sets a non-default background colour for the Button that the mouse pointer is hovering over).
Final step. Go back into the Class module and we need to add a line of code to the Click event handler that calls the ButtonClicked procedure in the code-behind of the UserForm (this is why we passed UserForm1 into the SetUpCommandButton procedure i.e. to give the CCmdBtnEvents instance a reference to the UserForm that is being displayed). When that line is added (replacing the comment we put there initially) the Click event handler will look like this:
You're done. Display the UserForm and you will see that:
Every Button responds with a slight colour change when the mouse pointer hovers over it, and
Every Button responds by hiding the UserForm and displaying the letter that was displayed on it when it is clicked
Notes
You cannot handle all events in this way … obviously Click and MouseMove you can because we just did! In fact, the majority of events you can handle this way - but you cannot handle the events that belong to the MSForms.Control object and those events are: AfterUpdate, BeforeUpdate, Enter and Exit.
I find it really (REALLY) tedious having to navigate around my code every time the UserForm has been displayed (you know, when you ran your code the VBE was displaying the code you were working on but after your code completes you're looking at the UserForm designer). You then have to manually click and scroll around to get back to where you were. Frustrating. But there is a better way - you can use VBE_Extras to 'Set a Sub to run' and just use the F6 key to run your code without having to navigate to it first (this works even when you are working within the UserForm designer). You can also use VBE_Extras to allow you to navigate back to your previous editing position with one keystroke / menu selection, jumping you back from the UserForm designer to your last editing position. Give VBE_Extras a try!
If you want to rename the UserForm or the Class module then you must change their references (ie the names that are referring to them) in the code (they both appear twice in the code) as well as changing the name of the UserForm / Class module itself. If you want a quicker way to do this (in fact a quicker way to rename any declaration and all of its references), see my post Refactor / rename in VBA.
Comments