Avatar of GAILBAKER
GAILBAKER
 asked on

Excel 2010 Macro Buttons on all worksheets of file

Hi

I can create a Macro button on a particular sheet of an Excel 2010 file, no problems.

But, is there a way to have the button display on all current and future sheets of the file (I need to import new sheets from another source as well as the current ones)?

The macros are recorded to the particular file - I don't want to save them to the Personal Workbook file or the Excel program.

Many thanks

Gail
Microsoft Excel

Avatar of undefined
Last Comment
GAILBAKER

8/22/2022 - Mon
byundt

Gail,
Here is a macro that will check each worksheet when activated to make sure it contains a command button named MacroButton. If not, a Forms control command button is added on top of cell G1. The new buttons are linked to a macro called MyMacro.

No matter how you add a worksheet to the workbook, it will have a command button added to it by the macro.
'This code must be installed in ThisWorkbook code pane. It won't work at all anywhere else!
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim shp As Shape
Dim ws As Worksheet
With Sh
    On Error Resume Next
    Set shp = .Shapes("MacroButton")
    On Error GoTo 0
    If shp Is Nothing Then
        Set shp = .Shapes.AddFormControl(xlButtonControl, .Range("G1").Left, .Range("G1").Top, 100, 23)
        shp.Name = "MacroButton"
        shp.TextFrame.Characters.Text = "Run macro"
        shp.OnAction = "MyMacro"
    End If
End With
End Sub

Open in new window

Note that the macro must be installed in the ThisWorkbook code pane. It won't work at all if installed anywhere else.

Brad
byundt

Another approach is to add the button to the ribbon. If you do, the button will be there for all worksheets when that workbook is active, then disappear when you work on other workbooks. The biggest advantage of this approach is that the button is always visible, no matter where you scroll on the worksheet.

To create a button on the ribbon, you'll need to edit the CustomXML part of the workbook. You can find a tool to do so on Andy Pope's web page: http://andypope.info/vba/ribboneditor_2010.htm
GAILBAKER

ASKER
Thanks Byundt - I'll give it a go.  Gail
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rory Archibald

If it's only for your use you could also customise the Quick Access Toolbar and add the macro button to it, specifying that it should only be available to that workbook.
GAILBAKER

ASKER
The problem is that it has to be all "self-contained" within the file (as it is a file that has to go to various computers), and it has around 30 macros!!

Is the Andy Pope Add-in only necessary for my computer while I am creating the custom Ribbon, or would the Add-in have to be on the other computers also??

Thanks
Rory Archibald

It's only necessary while creating the ribbon. All the customisation is stored in the file itself.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GAILBAKER

ASKER
That's terrific - thanks.  Calling it a day now so will try and design it tomorrow.  Will let you know how it goes!

G
Rob Henson

Alternatively, you could have a User Form containing buttons for each macro.

The User Form can have multiple tabs so you wouldn't necessarily have to have all 30 buttons on one form. The form can be set to be visible at all times once the workbook is opened or on activation of specific sheets if the macros only need to be available on certain sheets.

Thanks
Rob H
ASKER CERTIFIED SOLUTION
GAILBAKER

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GAILBAKER

ASKER
OK, understand, but thanks all the same as I think yours was probably the best!!

G
Your help has saved me hundreds of hours of internet surfing.
fblack61