asked on 4/4/2013

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

Last Comment

GAILBAKER

4/8/2013

byundt

4/4/2013

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

4/4/2013

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

4/4/2013

ASKER

Thanks Byundt - I'll give it a go. Gail

Rory Archibald

4/4/2013

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

4/4/2013

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

4/4/2013

It's only necessary while creating the ribbon. All the customisation is stored in the file itself.

GAILBAKER

4/4/2013

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

4/4/2013

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

4/8/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GAILBAKER

4/8/2013

ASKER

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

G