Macro to add buttons to every sheet except first sheet

[holt3130]

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.

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

 

[BobUmlas]

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]

Is this what you're looking for?

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]

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]

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]

This is what I ended up using and it works fine.

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:

 

[mikerickson]

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.

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)

 

[mikerickson]

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.

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]

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]

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.