Assigning macros to buttons on all worksheets

[The Skipper]

Time for the experts on this one )

I have a workbook that contains approximately 100 identical worksheets (including buttons). I want to assign each button on each worksheet to a macro. i tried to do it by selecting a tab and then "Select All Sheets". It seems that "Select All Sheets" deactivates when I right click on the macro button I want to assign a macro to.

I'd sure appreciate your help. Thanks !!

[Richard Buttrey]

Hi,

Do the buttons all run the same macro?

If so why not create a macro to copy the button and then loop through all the sheets and paste it.

Regards

[The Skipper]

Richard~

I think I misunderstood your question. The answer is yes. I want the buttons on each worksheet to access the same four macros.

[Richard Buttrey]

Hi,

That's fine. It doesn't alter the general technique, just copy and paste all four buttons within the loop that progresses through all the sheets.

Regards

[The Skipper]

Please give me a helping hand with the code.

[Richard Buttrey]

Hi,

Assuming this is a Forms Control Button then add your four buttons on Sheet1 and assign them to their macros. Then run the following macro

Sub CopyFormButton()
   Sub CopyFormButton()
    Dim x As Long, y As Long, stCell

    stCell = Array("B5", "B10", "B15", "B20") ' set cell ref for positioning button

    
    For x = 2 To Sheets.Count
        Sheets(x).Activate
        For y = 1 To 4
            Sheets("Sheet1").Shapes("button " & y).Copy
            Range(stCell(y - 1)).Select
            ActiveSheet.Paste
        Next y
    Next x

End Sub

[The Skipper]

Richard~

There are 4 buttons on each worksheet, I want to link them to 4 different macros.

Thanks !!

[The Skipper]

Thanks Richard..........You're getting 5 starts !!
Works perfectly.

[Richard Buttrey]

Hi,

Thanks for the rep point.

Just as an aside, don't forget that you can also use all the drawing shapes as 'buttons'. You don't necessarily need the form or activex buttons.
I generally prefer these since drawing shapes since they offer more designs and formatting options and generally look smarter and prettier.

Regards