Another n00b question

[shetzel]

OK.. I was able to get the previous post resolved successfully. (Thanks!)

Now I've got another doozy of a question. Is there a way to dynamically create sub-procedures?

Here's the scenario... I'm attempting to create a scalable database, where fields/controls can be added/deleted at will, and still preform it's functions. What I want the code to do is upon creation of control X, create the on-change function of control X.

Example:

Private Sub X_Change()
Call ColorsUpdate
End Sub

Everything in the function stays the same with the exception of the control name. X needs to be replaced with whatever the new control is called, and this needs to happen everytime a new control is added.

Can this be done? Thanks for all your help!

 

[VBAjedi]

AFAIK, there is no "Control_Add" event to trigger your code. . .

The only thing I can think of that would be even remotely close would be to use the Worksheet_Open event to loop through all the controls, checking each one to see if it has the correct code attached. But that would be touch-and-go. . .




VBAjedi

 

[TonyJollans]

Hi shetzel,

Firstly, what Application are you talking about, Excel or Access?

Secondly I'm pretty sure VBAjedi is right when he says there is no event that you can trap, but how are the controls added and to what? (in Access I guess it's just Forms, in Excel are the controls on a Sheet or a Userform).

It is possible to dynamically add procedures; the difficult bit is knowing when to do it. Also, if controls are deleted, do you want to remove procedures? Possible, but quite a bit harder.

Sounds like an ambitious undertaking.

Enjoy,
Tony