Dynamic VBA coding - How to manipulate VBA components at runtime?

Question

I'm trying to learn how to dynamically add components to a VBA user form, add new code dynamically, and then run the code. John Walkenbach's book "Power Programming with VBA" covers this topic in Chapter 28 "Manipulating Visual Basic Components", but I can't get the examples to completely work.

I have no problem getting the code to work to add new objects to a form. I also don't have any problem getting InsertLines to add dynamic code to the form's module. The code appears in the form's module, just like I'd like.

So the command button shows up on the form and the Click Event shows up in the form module and looks like this:

Private Sub CommandButton1_Click()
MsgBox "Hello"
End Sub

But when I click on CommandButton1, it is like Excel doesn't even know I've written a Click Event for the button. I've made sure to reference the Microsoft Visual Basic for Applications Extensibility library. And I made my changes to the macro settings to trust access to the vba project object model. But it still doesn't work!

Does anyone know why it isn't recognizing my Click Event? I'm using his 100 CommandButtons example and it just seems like something just was forgotten to be mentioned in the book or something. Either that or I'm doing something wrong.

I know this isn't the easiest of topic, but I'd greatly appreciate any help because I'm trying to use this concept to add more text boxes and combo boxes to a form in a VBA project that I'm working on. My complex form has 17 rows of objects. Each row enters a line of instructions on an Excel worksheet. But with 17 rows of objects, the form is getting pretty big. So I created a spin button to hide the first row and add a new row and move the rest of the rows up. Selections of the combo boxes bring up other forms to quickly enter data for making calculations and such. So when I add a new row, the new objects have to have event procedures that work to bring up the new forms. I've solved adding the objects, etc...just not getting the events to work. I've thought about creating a work-around just staying with 17 instruction rows and not adding any new objects and just storing the data to an array to work with for certain portions. So for example if I add an 18th row, the 1st row that would have gotten hidden would get added to the array. But that would involve completely rewriting major portions of my program to work with the array data instead of the form's objects. So if someone can help me out this would save me a lot of extra work.

Answer 1

Hi Devilish,



I have only adventured into this VBA area a little bit, so you are much farther along than I am. My only suggestion would be to join the Peach.ease advanced Excel L list forum and post the question there. There are some 'extreme' Excel folks there who can probably provide some assistance; Bob Umlas and Don Gullet to name two. I have been subscribed to the list for years and have received timely, thorough, and spot on answers on many occasions. There is a searchable archive available to subscribers that is quite extensive.



Take a look and see what you think.



http://peach.ease.lsoft.com/archives/excel-l.html



garbo

Answer 2

You question is too big to understand but if your events are not working check if you are still

not on design mode before you click the command button also if your command button is

on Sheet1 then if you are adding your code manually make sure you add your code for

Sheet1 on vba editor

Answer 3

Yahoo Answers is pretty good, but I think you're more likely to get a good answer by posting to the Microsoft Developer Network (MSDN) forums.

Answer 4

The prevalence of vivid pokemon without gameshark codes isn't available.. it doen't remember on success in any respect..... NO CODE - NO vivid POKEMON is the perfect diagnosis... im one hundred% positive/.....