How to make a commandbutton show in all sheets?

Hey,

can someone tell me how I make buttons show in all sheets and in all newly created sheets?

I have about 10 sheets wit the same commandbuttons with these same codes asigned to them by copying these codes for every sheet.

Private Sub CommandButton1_Click()
On Error GoTo err_handler
    ActiveWorkbook.FollowHyperlink Address:="\\private\workbook.XLS", SubAddress:=" 'sheet1'!a1", _
    NewWindow:=True
Exit Sub
err_handler:
End Sub

Private Sub CommandButton2_Click()
On Error GoTo err_handler
    ActiveWorkbook.FollowHyperlink Address:="\\private\workbook.XLS", SubAddress:=" 'Sheet2'!a1", _
    NewWindow:=True
Exit Sub
err_handler:
End Sub

Private Sub CommandButton3_Click()
On Error GoTo err_handler
    ActiveWorkbook.FollowHyperlink Address:="\\private\workbook.XLS", SubAddress:=" 'Sheet3'!a1", _
    NewWindow:=True
Exit Sub
err_handler:
End Sub

Private Sub CommandButton4_Click()
On Error GoTo err_handler
    ActiveWorkbook.FollowHyperlink Address:="\\private\workbook.XLS", SubAddress:=" 'Sheet4'!a1", _
    NewWindow:=True
Exit Sub
err_handler:
End Sub

Private Sub CommandButton5_Click()
On Error GoTo err_handler
    ActiveWorkbook.FollowHyperlink Address:="\\private\workbook.XLS", SubAddress:=" 'sheet5'!a1", _
    NewWindow:=True
Exit Sub
err_handler:
End Sub
Private Sub CommandButton6_Click()
On Error GoTo err_handler
    ActiveWorkbook.FollowHyperlink Address:="\\private\workbook.XLS", SubAddress:=" 'sheet6'!a1", _
    NewWindow:=True
Exit Sub
err_handler:
End Sub

Can I not put these codes in the "This workbook" and only make these commandbuttons on sheet1. So all the other sheets display these buttons to and also work with the codes?

No, that wouldn't work. Why not put the button on a toolbar instead - then you only need one?

That doesn't matter. You can have the workbook create the button when it's opened and delete it again when closed? Would that work for you?

You will need to move your code to a macro in a normal module. Then add a new normal module and paste this in - you need to alter the caption and macro name constants at the top as necessary:

Option Explicit
' this is the name of the toolbar - you can leave this as is or change it
Const mcstrTOOLBAR_NAME As String = "MyHyperlinkButton"
' this is the button caption - change as required
Const mcstrBUTTON_CAPTION As String = "Click me"
' this is the name of the macro that the button will run - change as needed
Const mcstrBUTTON_MACRO As String = "ClickHyperlink"

Sub AddMenu()
    Dim cbr As CommandBar, ctl As CommandBarControl
    On Error Resume Next
    Application.CommandBars(mcstrTOOLBAR_NAME).Delete
    On Error GoTo err_handle
    ' create toolbar
    Set cbr = CommandBars.Add(mcstrTOOLBAR_NAME, , , True)
    ' add button to it
    Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
        .Caption = mcstrBUTTON_CAPTION
        .OnAction = mcstrBUTTON_MACRO
        .Style = msoButtonCaption
    End With
    ' position toolbar and show it
    With cbr
        .Position = msoBarTop
        .Visible = True
    End With
    
    Exit Sub
    
err_handle:
    MsgBox Err.Description
End Sub
Sub RemoveMenu()
    On Error Resume Next
    Application.CommandBars(mcstrTOOLBAR_NAME).Delete
End Sub
Sub HideMenu()
    On Error Resume Next
    Application.CommandBars(mcstrTOOLBAR_NAME).Visible = False
End Sub
Sub ShowMenu()
    On Error Resume Next
    Application.CommandBars(mcstrTOOLBAR_NAME).Visible = True
    If Err.Number <> 0 Then AddMenu
End Sub

then in the ThisWorkbook module you need code like this:

Option Explicit

Private Sub Workbook_Activate()
    ShowMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveMenu
End Sub

Private Sub Workbook_Deactivate()
    HideMenu
End Sub

Private Sub Workbook_Open()
    AddMenu
End Sub

If you need more than one button, then you need multiple passes of this bit of the code:

    Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
        .Caption = mcstrBUTTON_CAPTION
        .OnAction = mcstrBUTTON_MACRO
        .Style = msoButtonCaption
    End With

changing the caption and macro name each time (or use the same macro and pass different parameters).
Without really knowing the details of what you are doing, I can't be more specific.

    Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
        .Caption = "TEST"
        .OnAction = "TEST"
        .Style = msoButtonCaption
    End With

I get more buttons but they state all false?

UPDATE: allright I got it working
thx man

You shouldn't have the constant in there:

   Set ctl = cbr.Controls.Add(msoControlButton, , , , True)
    With ctl
        .Caption =  "BUTTON1"
        .OnAction = "MACROBUTTON1
        .Style = msoButtonCaption
    End With

FYI, for multiple buttons, you might want to have a look at this article by John Walkenbach for a table-driven approach.