Macro to add buttons to every sheet except first sheet

holt3130

New Member
Joined
Jul 23, 2013
Messages
14
I have an excel file where the first sheet is an index of all the sheets in the workbook.
I need a macro that adds three buttons to every sheet except for the first sheet (the index)

I will need to run this macro again and again as new sheets are added. Rather than searching for the new sheets and adding the buttons the macro would just run on every sheet again so it will need to delete any buttons previously added and add the buttons again so that it is not adding buttons on top of buttons for the sheets that were already in the workbook.

This is what I have so far but I'm not sure how to skip the first sheet and how to delete all buttons before adding the buttons again.

Code:
Sub NextSheet()
ActiveSheet.Next.Select
End Sub
Sub PrevSheet()
ActiveSheet.Previous.Select
End Sub
Sub firstsheet()
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("All Specs").Select
End Sub
Sub addButton()


Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Activate
ActiveSheet.Buttons.Add(650, 18, 60, 25).Select
Selection.OnAction = "PrevSheet"
Selection.Caption = "Previous"
ActiveSheet.Buttons.Add(730, 18, 60, 25).Select
Selection.OnAction = "NextSheet"
Selection.Caption = "Next"
ActiveSheet.Buttons.Add(810, 18, 60, 25).Select
Selection.OnAction = "firstsheet"
Selection.Caption = "To Index"
Next wks




End Sub
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
In AddButton procedure after the For Each...line, enter this:
If wks.Index=1 then goto Notthis
and put this line just before the Next wks line:
NotThis:
 

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
969
Is this what you're looking for?

Code:
Sub CommandButton2_Click()

For Each Worksheet In ActiveWorkbook.Worksheets
    With Worksheet
        If .Name <> "Sheet1" Then
            Exit Sub
        Else
            OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=300, Top:=153, Width:=72, Height:=24).Select
            OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=450, Top:=153, Width:=72, Height:=24).Select
            OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=600, Top:=153, Width:=72, Height:=24).Select
        End If
    End With
Next Worksheet


End Sub
 

holt3130

New Member
Joined
Jul 23, 2013
Messages
14
Thank you. This will work, but It needs to also delete the buttons that were already on each sheet so that I can run the macro again when a new sheet is added.

I guess I would try putting:

ActiveSheet.Buttons.Delete

at the begining of the For Each after your suggestion?

I'll give that a shot.
any suggestions though would be helpful.
 

BrianExcel

Well-known Member
Joined
Apr 21, 2010
Messages
969

ADVERTISEMENT

Code:
Sub CommandButton2_Click()

For Each Worksheet In ActiveWorkbook.Worksheets
        If Worksheet.Index <> 1 Then
            ActiveSheet.DrawingObjects.Delete
            OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=300, Top:=50, Width:=72, Height:=24).Select
            OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=450, Top:=50, Width:=72, Height:=24).Select
            OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=600, Top:=50, Width:=72, Height:=24).Select
        End If
Next Worksheet


End Sub


Only word of caution - if you have other buttons on the sheets (such as one that triggers the code written above), it's going to delete and re-add that one too.
 

holt3130

New Member
Joined
Jul 23, 2013
Messages
14
This is what I ended up using and it works fine.


Code:
Sub NextSheet()
ActiveSheet.Next.Select
End Sub

Sub PrevSheet()
ActiveSheet.Previous.Select
End Sub

Sub firstsheet()
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("All Specs").Select
End Sub

Sub addButton()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
wks.Activate
If wks.Index = 1 Then GoTo NotThis
ActiveSheet.Buttons.Delete
ActiveSheet.Buttons.Add(650, 18, 60, 25).Select
Selection.OnAction = "PrevSheet"
Selection.Caption = "Previous"
ActiveSheet.Buttons.Add(730, 18, 60, 25).Select
Selection.OnAction = "NextSheet"
Selection.Caption = "Next"
ActiveSheet.Buttons.Add(810, 18, 60, 25).Select
Selection.OnAction = "firstsheet"
Selection.Caption = "To Index"
ActiveSheet.Range("A1").Select
NotThis:
Next wks




End Sub
for those that may stumble upon this later, this macro when run does this:

skips the first sheet of the workbook
deletes all buttons on the worksheet
then adds three buttons
one goes to the previous sheet
one goes to the next sheet
and one goes back to the first sheet
then repeats for all sheets

the only thing that should be changed for others is the name of the first sheet in the "firstsheet()" sub (you could alter this code to go to the first sheet I guess without needing to use the sheet name)
and the positioning of the buttons in the "addButton()" sub

this is the result:
Zstb8SpnGfT0XoPzUTXD9Kc_0hcIxMFjNXRKRCSLzdIsPx57VIC0pIqRK5MprP-S_knBpWtIteU=w1256-h779
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

I would add buttons to the existing sheets and then put code in the Workbook_NewSheet event to add buttons to that sheet.
Put this in the ThisWorkbook code module, and you don't have to worry about looping or deleting buttons, they will be added to every subsequent new sheet.
Code:
Public Sub Workbook_NewSheet(ByVal Sh As Object)
    With Sh
        With .Buttons.Add(650, 18, 60, 25)
            .OnAction = "PrevSheet"
            .Caption = "Previous"
        End With
        With .Buttons.Add(730, 18, 60, 25)
            .OnAction = "NextSheet"
            .Caption = "Next"
        End With
        With .Buttons.Add(810, 18, 60, 25)
            .OnAction = "firstsheet"
            .Caption = "To Index"
        End With
    End With
End Sub
Note, copying a worksheet does not trigger the NewSheet event. You would have to call it explicitly if you copy worksheets to create them (but the copy should have the buttons on it already so, maybe not)
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Here is a set-up routine. Put this in a normal module, run it once, delete it and the Workbook_NewPage event will take care of the new sheets.
Code:
Sub test()
    Dim oneSheet As Worksheet, oneButton As Shape
    For Each oneSheet In ThisWorkbook.Worksheets
        If oneSheet.Index <> 1 Then
            For Each oneButton In oneSheet.Shapes
                If oneButton.Type = msoFormControl Then
                    Select Case oneButton.TextFrame.Characters.Text
                        Case "Previous", "Next", "To Index"
                            oneButton.Delete
                    End Select
                End If
            Next oneButton
            
            With oneSheet
                With .Buttons.Add(650, 18, 60, 25)
                    .OnAction = "PrevSheet"
                    .Caption = "Previous"
                End With
                With .Buttons.Add(730, 18, 60, 25)
                    .OnAction = "NextSheet"
                    .Caption = "Next"
                End With
                With .Buttons.Add(810, 18, 60, 25)
                    .OnAction = "firstsheet"
                    .Caption = "To Index"
                End With
            End With
        
        End If
    Next oneSheet
End Sub
 

holt3130

New Member
Joined
Jul 23, 2013
Messages
14
Here is a set-up routine. Put this in a normal module, run it once, delete it and the Workbook_NewPage event will take care of the new sheets.

Though it may not be the case for others, my reason for using the loop every time is because the workbook I use it in has several hundred sheets. When a new sheet is added I am not the one adding the sheet and it is almost never the last sheet in the workbook. so instead of searching through hundreds of sheets to find the new ones without buttons, it is easier for me to just run the macro on all sheets again.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
The Workbook_NewSheet event doesn't care where the new sheet is added, or by whom. It runs automatically.
It doesn't have to be called, it just runs.

Take a new workbook, add the Workbook_NewSheet code to the ThisWorkbook module. Add some sheets.
Try the code. Try to make it fail.

(Note that the linked routines won't be in the new workbook, so the buttons will do nothing.)

After the set up routine is run, you will never have to do anything, the buttons will be there on every new sheet.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top