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

 

[holt3130]

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.

Ah yes, now I understand.

works great!

Thank you!