Use Macro To Change Macro Assigned To Command Button

[CVinje]

My worksheet has 42 different command buttons with macros assigned to them. I need to change the macro assigned to those command buttons using another, separate macro. I know how to change the assigned macro if I were using just buttons (macro recorder showed me), but I don't know how to change the assigned macro when we're talking about command buttons. If this is not feasible, a find / replace would work on the existing macro that is already assigned to the buttons, but actually changing the macro assigned in preferred. The command button is on the sheet itself, not in a UserForm or otherwise. Any help would be appreciated.

Thanks,

CVinje

[pike]

Hi CVinje
depends but something like

Please Login or Register  to view this content.

Please Login or Register  to view this content.

sub are call testthree and testtwo

[foxguy]

Scratch this message, the macros weren't tested properly. See message later on.

[CVinje]

Not really sure what to do with the provided macros - I seem to lack the knowledge to implement them accordingly. The first part of the first macro posted does create a new button; however it stops with error "Run-time error '1004' ; Unable to set the OnAction property of the Button class. The other issue is that the easiest solution would be to simply reassign the macro already associated with the Command Button on the page instead of creating new buttons. The problem I'm running into is that the existing buttons are Command Buttons (which is desired for formatting options, etc). It looks as though I may have to give up on retaining the format of Command Button and go with a simple button to accomplish this; however, I just can't believe that there is no straight forward answer for how to accomplish what I need via a macro (I can believe that I'm not educated enough to solve the problem my self, or ask the question properly the help others help me ).

I really appreciate your reply and apologize for my late response; however, I did not foresee being away from a computer for such an extended period of time. Any further help would be greatly appreciated.

CVinje

[foxguy]

CVinje;

Not really sure what to do with the provided macros

I'm sorry, I tried simplifying my macros, but I did it here in Excel Forum without testing them. I just assumed they would work like they do in my workbooks. They don't, so here they are after testing.

I'm assuming that you don't know how to modify your menus. If that's wrong, I apologize.

1. Copy all these subs/functions into a Standard Module.

2. Run "Create_MenuButton_For_Button_OnAction()". It will put a temporary button on your Menu Bar with the Caption "Set_Button_OnAction". The button will be removed when you close Excel.

3. Select a button with your right mouse button. This will put a shadow around the button.

4. While the button is selected, click "Set_Button_OnAction" on your menu. This will assign a macro "Button_Clicked()" to the button.

5. Click the button (you'll have to move your cursor to another cell 1st). This will stop inside "Button_Clicked()" where you can tell VBA where you want the button to go.

6. Create a "Case <button caption>" and your real sub() name inside "Button_Clicked()"

7. Go back and click the button again and it should go to the sub you want to assign.

8. Do steps 3 - 7 with another button.

When you create a new button, assign the macro "Button_Clicked()" to it, and put the real sub() inside Button_Clicked(), and you can then change it anytime you want without having to modify the button itself. You can use my "Create_Button()" macro to do that with just one mouse click.

Please Login or Register  to view this content.