I have workbook contain about 45 sheets, and I need to create one Summary
sheat containing buttons for names of all sheats.
The qustion is How to make a each Button open a specified sheet.
best regards

Private Sub CommandButton1_Click()
Sheets("Sheet2").Activate
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"NIDAL" wrote:

I have workbook contain about 45 sheets, and I need to create one Summary
sheat containing buttons for names of all sheats.
The qustion is How to make a each Button open a specified sheet.
best regards

Rather than add lots of buttons to a worksheet use a custom toolbar with
single button. This when pressed, will produce menu of all worksheets in your
workbook - click on required sheet to goto to it.

ensure code is pasted where instructed

Hope helpful


''''''''Palce this code in thisworkbook''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''' '''
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Get Sheet").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
createmenubar
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''

''''''''place this code in standard module''''''
''''''''''''''''''''''''''''''''''''''''''''''''
Sub createmenubar()

On Error Resume Next
Application.CommandBars("Get Sheet").Delete
On Error GoTo 0

With Application.CommandBars.Add
.Name = "Get Sheet"
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop

With .Controls.Add(Type:=msoControlButton)

.Width = 100

.OnAction = "SelectSheet"

.Caption = "Select Sheet"
.Style = msoButtonIconAndCaption

End With


End With
End Sub

Sub SelectSheet()
Dim ws1 As Worksheet
If ActiveWorkbook.Sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If

Set ws1 = Worksheets(ActiveSheet.Name)
End Sub

--
jb


"NIDAL" wrote:

I have workbook contain about 45 sheets, and I need to create one Summary
sheat containing buttons for names of all sheats.
The qustion is How to make a each Button open a specified sheet.
best regards