Jump to A Sheet

[caveman.savant]

I have a Excel file that contains several worksheets. Can I create a
"homepage" sheet with links to the other sheets?

 

[Don Guillett]

I do this with a "menu" sheet and the other sheet names typed in. Then right
click on the cell with the name.
Right click sheet tab>view code>insert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
' GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a1")
End If
Application.DisplayAlerts = True
End Sub

 

[caveman.savant]

I do this with a "menu" sheet and the other sheet names typed in. Then right
click on the cell with the name.
Right click sheet tab>view code>insert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
 Dim WantedSheet As String
   WantedSheet = Trim(ActiveCell.Value)
   If WantedSheet = "" Then Exit Sub
   On Error Resume Next
   If Sheets(WantedSheet) Is Nothing Then
   '   GetWorkbook ' calls another macro to do that
   Else
      Application.GoTo Sheets(WantedSheet).Range("a1")
   End If
 Application.DisplayAlerts = True
End Sub

Can I use Form Buttons on this Page instead of cells?

 

[Gord Dibben]

Yes, you can but how will you get back to the "homepage"?

Rather than a menu page...................

You can build a Navigation Toolbar as per Dave's code on Debra Dalgleish's
site. Sorts as well as navigates.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP

 

[Roger Govier]

Can I use Form Buttons on this Page instead of cells?

No, not with the code Don has supplied.
If you use Form buttons, you would need separate macros which you would have
to assign to each button with something like

Sub SelectSht1()
Sheets("Accounts 08").Activate
End Sub

Alternatively, you could use Hyperlinks
With Sheet name in a cell>Right click>Insert>Hyperlink>Link to>Place in this
Document>choose Sheet location>Enter a cell Reference