Option Explicit

Private Sub Workbook_Open()
    Dim i           As Long
    Application.ScreenUpdating = False
    ThisWorkbook.Worksheets("Sheet1").Range("B2:B" & ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row).ClearContents

    For i = 1 To Sheets.Count

        If ThisWorkbook.Sheets(i).Name <> "Sheet1" Then
            Application.DisplayAlerts = False
            ThisWorkbook.Worksheets("Sheet1").Cells(i, 2) = Sheets(i).Name
            Application.DisplayAlerts = True
        End If

    Next

    Application.ScreenUpdating = True
End Sub

Option Explicit

Private Sub Worksheet_Activate()
    Dim i As Long, iRow As Long
    Dim t           As Range
    Dim btn         As Button
    iRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
    Application.ScreenUpdating = False
    ActiveSheet.Buttons.Delete

    For i = 2 To iRow
        Set t = ActiveSheet.Range(Cells(i, 3), Cells(i, 3))
        Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)

        With btn
            .OnAction = "btnS" & i
            .Caption = ActiveSheet.Cells(i, 2).Text
            .Name = "CommandButton" & i
        End With

    Next i

    Application.ScreenUpdating = True
End Sub