Creating event handlers dynamically
Normally, when creating a control on a form within the development environment, you assign your code for event handling through the control. However, when your controls are created dynamically, clearly you cannot do this. This means that the event handlers have to be created dynamically also. To see how this was applied to Roadmap Generation see jSon and Dynamic Forms
There are a number of ways of doing this,
- Create the code through the IDE at run time by generating code and inserting into the correct module. An example of this approach is used in the code optimizer project in this site, where profiling code is introduced to your modules by selection from a form. This is complex and can suffer from different treatments in different versions of excel when applied to form controls. I briefly considered, then rejected it as an approach
- By creating a dedicated Class to handle all events related to the dynamic controls. This is an elegant and simple capability which is built right in to VBA, but is rather little used. This is the approach we will use.
Creating a class to handle events
The first step is to create a new class – I have called it
cHandleControlEvents. The initial lines of that class are as follows.
Option Explicit Private WithEvents ptb As MSForms.TextBox Private pEventSelected As Control
Once you have entered the 2nd line, if you go to the leftmost IDE drop down, you will see that the variable defined as ‘
WithEvents‘, in this case ptb, magically appears following (General).
This is pretty cool, since the other thing that happens is that all the events associated with the type of that variable, in this case
Msforms.textbox, appears in the other dropdown box.
So now you can associate any code with any textbox event. In this case, you can see I have implemented the KeyPress, MouseDown and MouseMove events.
What will do next is to create an instance of this class for every textbox control that we create dynamically. When we do that we need to be able to associate the control with the instance of the class, so we need to be able to set the ptb variable with a set property as follows
Public Property Set Control(p As MSForms.TextBox) Set ptb = p End Property
Creating instances of the event handling class
Now that we have the class defined, the next step will be to create an instance of that class for every textbox that we want to handle, as well as collection in which to store them. So we’ll do that in our main code. In this case, I handle creation of textboxes through the cFormGrid Class, so i add a new collection variable to that class.
Option Explicit Private pControlEvents As Collection
and finally create the instance, and assign the control to it as below.
' create a textbox Set ptb = puForm.Controls.Add("forms.textbox.1", tbName(sGrid.Item(idx))) Set ptbGrid(idx) = ptb Set SelectedIdx = ptb ' create a handler for this object and add to collection Set cHandler = New cHandleControlEvents Set cHandler.Control = SelectedTb Set cHandler.FormGrid = Me pControlEvents.Add cHandler
All that remains then is to ensure you can identify which control is being handled (I have named the controls with meaningful names to help do that), and handle the event as normal.
The complete code for this is in the Sudoku project and can be downloaded here. The classes that contain the code are cFormGrid and cHandleControlEvents
Jump to next topic to understand Prettifying dynamic grids