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,
The first step is to create a new class - I have called it cHandleControlEvents. The initial lines of that class are as follows.
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
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.
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
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Dynamic Forms >