Easy way to assign Macro button

[rizmomin]

Hello:

I have lots of sheets with monthly sheet.
In each sheet a have assigned a Macro button to summary the monthly report and all the sheets have the same macro.
Now i am changing the name of macro and this is causing me to reassign each macro button in each sheet to the new macro name.
I wonder if there is easy way to assign the new macro to all sheets in one go.

I could assign the name of the macro and its location on each sheet at certain same location if needed.

Please let me know if you have any questions.
Thanks.

Riz

[HaHoBe]

Hi, Riz,

what kind of buttons: Forms (as I assume) or ActiveX? Why not change the contents of the macro to suit instead of attaching a new one?

Ciao,
Holger

[rizmomin]

Hi HaHoBe:

This will be Forms and it is in my PERSONAL.XLSB


Please let me know if you have any questions.
Thanks.

Riz

[HaHoBe]

Hi, Riz,

that answered one of my questions but not why you donīt alter the code inside your personal workbook to suit.

Please Login or Register  to view this content.

You would need to alter the name of the button (should be the same on every sheet) and the name of the new macro (maybe add the name of the module as well if there are multiple macros with the same name - a no go for me!).

Ciao,
Holger

[rizmomin]

Hello:

May be its is misunderstood.
I have Macro name called Macro1,Macro2, etc in Perosnal.xlsb file.

i have assigned Macro1 to lots of sheet.
Now I realize to rename the "Macro1" to "MonthlyReport" so that it makes more sense for future editing.
So now i rename the "Macro1" to "MonthlyReport".
Now for each button i have assigned "Macro1" in the excelbook, i have to go back t each sheet and reaasign the macro button to macro called "MonthlyReport".
This is very time consuming and prone to error while assigning the correct Macro from list.

I hope this is clear, if not please do not hesitate to ask any questions.

Thanks

Riz

[rizmomin]

Hi HahoBe:

I see you have created VB Code for me to rename the macro.
Couple of questions.
This is what i have
5 seperate file and each file has macro within it self.
Now I am moving the macro in Personal.xlsb file so that it can be used universally.
I am seeing your code :

Const cstrBUTTON As String = "Button 1"

Now the question i have is how to assign where "Button 1" is
Lets say the name of the button is 2014-Store1_DailySales.xls'!Macro1" and it is in file "2014-Store1_DailySales.xls"


Please let me know if you have any questions.
Thanks.

Riz

[rizmomin]

Hello haHoBe:

I have used your code but for some reason nothing is happening, bascially it is not renaming.

PLEASE HELP.

Riz

[rizmomin]

Hello:

Please see below the code i am using as per your :

Please Login or Register  to view this content.

[HaHoBe]

Hiu, Riz,

I still doubt that all your buttons are named CDI_Mix_Paste but I will learn in a couple of more posts from you.

Please Login or Register  to view this content.

Ciao,
Holger

[rizmomin]

Hello Hahobe:

To simply please refer to attached sample workbook.
There are four sheets in the book.
All sheets have Button named X1 and is assigned to "RunSummary" macro.
Now i have updated and created another macro called "RunSummary_New" and want to assigned all macro Button from each sheet to this new macro.
I used your code to assign the macro on each sheet by our code and seems not to work.
Please help and let me know if any q.

Thanks

Riz

[HaHoBe]

Hi, Riz,

code for the last attachment would look like

Please Login or Register  to view this content.

as well as the macro codes being called like

Please Login or Register  to view this content.

Ciao,
Holger

[rizmomin]

Hi HahoBe:

Please refer to the attached file.
I have adopted exactly like you have mentioned but still not working.
Please look at it and let me know what i am missing to complete the help.
Would appreciate you can try and correct and upload working file.

Thanks

Riz

[HaHoBe]

Hi, Riz,

right-click the button and have a look at the NameField to get the correct number of the Button. No need to attach a workbook as you may change Button1 to Button 4 in the code.

Correct code for the last upload will look like:

Please Login or Register  to view this content.

If there only will be one button on the sheet you may try

Please Login or Register  to view this content.

Like stated: code may only work properly when one button is located on the sheet and not perform as expected when more than one button is located on the sheet.

Ciao,
Hiolger

[rizmomin]

Hello HiHoBe:

I have tried every thing according to your instructions.
But for some reason, it does not work and do noting.
Please help and let me know.
thx
Riz

[HaHoBe]

Hi, Riz,

the first code wonīt work as somebody missed an assignment about the names of the buttons. Check the last one provided as in the first sample you provided the button on sheet2 was named differntly while in the last sample the names were split.

Ciao,
Holger

[HaHoBe]

Hi, Riz,

maybe this macro is what you were looking for (it looops through all Forms Buttons on each shet and will only attach a new macro when it finds one hoilding the old one):

Please Login or Register  to view this content.

Ciao,
Holger

[rizmomin]

Hi HiHoBe:

I think you have asolved my problem with the new thread with little different output.
Once again, i thank you so much for such a great help.
Riz