Call one macro from multiple buttons - with specifics as needed

[Onyx]

I'm hoping that this has a simple solution, though it may get complicated.

I've searched through the forums for this information, and I have found a couple of similar issues that have been solved, but copy+pasting the code into my form has not had favorable results.

Basically, here is what I am after:

I have a sheet that already has 4 buttons active, with a handful of other subs that they are calling. I am wanting to be able to add new buttons and have them call a universal macro that will forward them to my existing subs. However, I'm wanting a variable to be set based on the Caption of the button that was clicked. For example:

Please Login or Register  to view this content.

This does not return a message box. I've tried a few different things with this and am unable to get the response.

I will also need a way to exclude one button from this. Maybe add a 'If ActiveControl.Caption = XXX then call sub1, endsub' line to the code?

Thank you very much in advance.

[foxguy]

I Use

Please Login or Register  to view this content.

[Onyx]

Just to clarify, is this to be wrapped in the sub like this:

Please Login or Register  to view this content.

If so, I am still not getting a msg box to pop at all when clicking on a button I just placed into the sheet.

[foxguy]

Try this then.

Please Login or Register  to view this content.

to see what the name of your button is.

[royUK]

What do you mean by forward to subs?

Attach an example workbook

[Norie]

What type of button(s) are you using?

How did you create them?

[foxguy]

If so, I am still not getting a msg box to pop at all when clicking on a button I just placed into the sheet.

Mis-read your message. You're not getting a msgbox at all. That imiplies that you didn't assign the correct macro to the button.

Right MouseClick on the button and "Assign Macro" and type in "ButtonEvents_Click"

[Onyx]

I have attached the draft workbook that I am using to get all of the macros/layouts correctly done.

I may not have been clear in describing what I am after - I am trying to avoid having to assign each button to a macro. In the attached workbook, I have the Test through Test5 buttons assigned to macros, setting a temporary tag and calling another macro. I have the Placeholder button NOT assigned specifically to test a universal call macro.

I'm going to end up with likely over 100 buttons, and am trying to avoid having to assign each one, since they will all do the exact same thing (with the variable tag of course)

Thank you again very much for the assistance.

EDIT: The reason I am going off of captions is because this is what is visible on the buttons - and they may need to be edited from time to time. With this setup, the actual 'name' of the button does not matter. I'm not sure if this is relevant or not.

[foxguy]

Here's a Macro that creates a button and assigns a universal macro to it.

Please Login or Register  to view this content.

Select the cell(s) that you want the button to be in and run the macro. It will fill the cell(s). I have it assigned to a button in my right mouse click menu,

[Onyx]

This is what I ended up with:

I created a macro Button_Click with the following:

Please Login or Register  to view this content.

This works with the macro you provided to create the buttons. The MsgBox is popping up, but it does not reflect the name of the button.

I also notice that this button does not have the same properties option when I go into design mode and right click it. Is there another way to set its "Caption" property? I tried just editing the text on the button itself and it is popping up a blank MsgBox.

[foxguy]

I'm not sure what you mean by going into design mode. Design mode in Excel? or VBE?

I have no trouble editing the text on the button itself and it changes the Caption property.

Add the red line to the macro and see what the name of the Caller is. If it doesn't start with "Button_" then sCaption will be empty.

Please Login or Register  to view this content.

[Onyx]

Placing the MsgBox line there does result in the message box popping up with Button_19. However, it still remains empty in the msgbox that is trying to display the caption.

By design mode I mean the menu that allows you to move/right click on the buttons you place from the View --> Toolbars --> Control Toolbox menu. This is how I was setting the names/captions of the other buttons I have on the form.

Edit: The smaller 'Place' button in the middle is the one I have that active macro working off of. It should display the blank MsgBox when clicked.

[foxguy]

Placing the MsgBox line there does result in the message box popping up with Button_19. However, it still remains empty in the msgbox that is trying to display the caption.

By design mode I mean the menu that allows you to move/right click on the buttons you place from the View --> Toolbars --> Control Toolbox menu. This is how I was setting the names/captions of the other buttons I have on the form.

Never noticed that toolbar before. I'll have to check it out.

Try this

Please Login or Register  to view this content.

When it stops step through 1 line at a time (F8) and see if it gets inside the "If ... End If" section.

[Onyx]

It was not getting into the If function.

I removed the "_" from the line entering it, so it ends up as:

Please Login or Register  to view this content.

It returns an error 1004:

"Unable to get the Buttons property of the Worksheet class"

[foxguy]

Try

Please Login or Register  to view this content.

[foxguy]

Just noticed a bug.

Please Login or Register  to view this content.

[Onyx]

Edit: Just saw the change you made in the above post. It is working! The msgbox now appears with the text I have typed into the name of the button.

I believe this is the answer that I needed to continue development of this project. Now that I have a variable that I can define the rest of it is just arranging and getting everything polished.

Thank you very much Foxguy for getting this solved!

Here is the code I ended up with:

To make a button:

Please Login or Register  to view this content.

The button calls the following macro:

Please Login or Register  to view this content.

This places the name of the button into the variable sCaption.

Thanks again!

[foxguy]

See my last entry where I discovered a bug.

The text is the Caption.