'Fixing' an Index Sheet

[stuartgb100]

I have a workbook containing a large number of sheets.
To more easily navigate through the book, I have an Index sheet containing hyperlinks to all the various sheet names.
This lets me 'jump' to the required sheet, and works well.
However, after 'jumping', the index sheet is no longer in view (is that in the taskbar - not familiar with the terminology !)
So I have to go back to the beginning to show all sheet names, before I can see 'Index' and click on it for the next required sheet.

Is there a way to 'fix' this so that the sheet 'Index' tab is always visible ?
Thanks.

 

[CephasOz]

You don't need the index sheet. At the bottom of the screen, to the left of the first sheet tab, you will see arrows: "< >". Right-click one of those arrows, and select the sheet you want, then click "OK". That sheet will be activated.

 

[Peter_SSs]

Alternatively, if the Index sheet is the first sheet and you cannot see it in the list, like in the image below, hold the Ctrl key and left click the left arrow.

You will then be scrolled to the very left of the sheet tabs where you will be able to see the Index sheet tab. If you have a great many sheets that could save you scrolling up the list shown by the previous suggestion.

(You can also use Ctrl + Left click on the right arrow to move to where you can see the very last sheet)

 

[Peter_SSs]

Yet another option might be this. Put the following vba code in the ThisWorkbook module for the workbook. Then if you double-click any cell in row 1 of whatever worksheet you are on, you will be returned to the 'Index' sheet.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
  If Target.Row = 1 Then
    Cancel = True
    Application.Goto Reference:=Sheets("Index").Range("A1"), Scroll:=True
  End If
End Sub

 

[stuartgb100]

Many thanks all for the help.
Much appreciated.

 

[Peter_SSs]

Cheers. Glad we could offer you a few options. Thanks for the follow-up.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[stuartgb100]

Cheers. Glad we could offer you a few options. Thanks for the follow-up.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Will do, thanks for the heads up.
Cheers.

 

[Peter_SSs]

No problem.
BTW ..

1. Are your hyperlinks on 'Index' created with the =HYPERLINK() function or through Insert ribbon tab -> Link or some other way?
2. Do the hyperlinks on 'Index' just show the relevant sheet name something like the image below?
3. When you click on one of the hyperlinks and go to that sheet, are you only ever working on/looking at that one sheet (& the 'Index' sheet)?

Reason for asking is that another option (assuming the hyperlinks were not done by the HYPERLINK() function) might be to hide all the worksheets except 'Index'. You could do that manually or by running this code.

Sub One_Off_Hide()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    ws.Visible = ws.Name = "Index"
  Next ws
End Sub

Then put this code in the 'Index' sheet's vba module

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  Sheets(Target.Name).Visible = True
  Sheets(Target.Name).Activate
End Sub

.. and this code in the ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  If Sh.Name <> "Index" Then Sh.Visible = xlHidden
End Sub

With the above set-up, when you clicked a hyperlink on 'Index' the relevant worksheet would become visible and down the bottom you would only have two tabs showing 'Index' and whatever sheet you had hyperlinked to. That would make it very easy to go back to 'Index' and when you did, the other visible sheet would again be hidden. Similar process repeated for each hyperlink you clicked.

If for some reason you wanted all the sheets visible again, this code would do it.

Sub One_Off_Unide()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    ws.Visible = True
  Next ws
End Sub

 

[stuartgb100]

Many thanks again for the help.
Your routines certainly give me more flexibility.
Yes, apart from the 'Index' sheet, I use about 5 other sheets very regularly.
I think I'll try as follows:
1. modify your routine such that when the workbook opens -
a) the 'Index' sheet is visible
b) only tabs for the 'Index' sheet and 'frequent sheets' are visible

2. find a way to remove a tab for a sheet called up via the 'Index' sheet
so......
a sheet is called via the 'Index' sheet and it's tab is now visible
I work on that sheet, and then click a different sheet tab
I suspect that the last sheet's tab will still be visible
After calling many 'hidden' sheets, there's going to be a lot of tabs visible
So I'd be back to square one, again
So I need to find a way to remove each tab when I leave those sheets.

Thanks again.
Cheers.

 

[Peter_SSs]

Try this with a copy of the workbook then

One-off code in a standard module to hide all except the "always visible" sheets

Sub One_Off_Hide()
  Dim ws As Worksheet
 
  Const AlwaysVisible As String = "|Index|Keep 1|Keep2|Data 1|Data 2|Ref|" '<- Amend if/as required
 
  For Each ws In Worksheets
    ws.Visible = InStr(1, AlwaysVisible, "|" & ws.Name & "|") > 0
  Next ws
  Sheets("Index").Activate
End Sub

One-off code in a standard module (could be same module as the one above) if you want to unhide all sheets.

Sub One_Off_Unide()
  Dim ws As Worksheet
 
  For Each ws In Worksheets
    ws.Visible = True
  Next ws
  Sheets("Index").Activate
End Sub

This code in the 'Index' sheet module

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
  Sheets(Target.Name).Visible = True
  Sheets(Target.Name).Activate
End Sub

This code in the ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
  Const AlwaysVisible As String = "|Index|Keep 1|Keep2|Data 1|Data 2|Ref|" '<- Amend if/as required
 
  If InStr(1, AlwaysVisible, "|" & Sh.Name & "|") = 0 Then Sh.Visible = xlHidden
End Sub