'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.

 

[stuartgb100]

Thanks again Peter.
Working perfectly !
I struggled at first, because my routine to populate the Index sheet would not fire.
I ended up putting it in the Workbook_Open event, and also added your code to show only the sheets that i wished to be visible.
Thanks again,
Cheers.

 

[Peter_SSs]

Cheers. Glad it is now sorted. Thanks for the follow-up.

Working perfectly !

So, just wondering ... Is the correct post marked as the solution?
I was thinking post 10 might be what you were referring to? (Just trying to help future readers of the thread)

 

[stuartgb100]

I'm using code from your later reply which begins ....
"Try this with a copy of the workbook then"

As said, it works fine.

I've tried to call the routines which hide/unhide sheets via a cells in the Index sheet, such that I can easily access hose subs from the open workbook.

However, I've hit a problem:

Code in Module 1 as follows:

Sub Hide_Selected_Sheets()
  Dim ws As Worksheet
 
'   this routine hides all sheets except the required sheets
  Application.ScreenUpdating = False
 
  Const AlwaysVisible As String = "|Index|Urgent|Shopping|Today|Tomorrow|This Week|This Month|Appointments etc|Ref|" '<- Amend if/as required
 
  For Each ws In Worksheets
    ws.Visible = InStr(1, AlwaysVisible, "|" & ws.Name & "|") > 0
  Next ws
  Sheets("Index").Activate
 
  Application.ScreenUpdating = True
 
End Sub

Sub Unhide_All_Sheets()
  Dim ws As Worksheet
 
  ' this routine unhides all sheets
  Application.ScreenUpdating = False
 
  For Each ws In Worksheets
    ws.Visible = True
  Next ws
  Sheets("Index").Activate
 
  Application.ScreenUpdating = True
 
End Sub

Code in sheet "Index":

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   this routine will unhide all sheets if cell C2
'   in the Indez sheet is clicked
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("c2")) Is Nothing Then
            Call Unhide_All_Sheets
        End If
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   this routine will unhide all sheets if cell C4
'   in the Indez sheet is clicked
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("c4")) Is Nothing Then
            Call Hide_Selected_Sheets
        End If
    End If
End Sub

When the workbook opens, I receive the following error:
Ambiguous name detected: Worksheet_SelectionChange
and this line is highlighted in the VBE:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I assumed that I might have a duplicate declaration somewher, but cannot see it.
What is my error please ?
Cheers.

 

[Peter_SSs]

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.

It hasn't happened yet.


When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I assumed that I might have a duplicate declaration somewher, but cannot see it.
What is my error please ?

I have highlighted it red in your previous post. You can only have one Worksheet_SelectionChange code in the worksheet's module.

What I think you want is to replace those two procedures with this one.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'   this routine will ..
'   - unhide all sheets if cell C2 in the Index sheet is clicked
'   - hide relevant sheets if cell C4 in the Index sheet is clicked
    If Selection.Address = "$C$2" Then
        Call Unhide_All_Sheets
    ElseIf Selection.Address = "$C$4" Then
        Call Hide_Selected_Sheets
    End If
End Sub

 

[stuartgb100]

My apologies !
Believe it or not, I just worked that out (a few hours sleep helped).
I came up with the same routine, although I used "C2" in lieu of $C$2

Embarrassing to make such a stupid mistake,
but pleasing to have managed to spot and correct it.

Details updated.

Thanks again.

 

[Peter_SSs]

although I used "C2" in lieu of $C$2

In that case you didn't use just Selection.Address as I did but there are several similar ways that you could do it. Main thing is you have it working.

The way to eliminate the $ signs with my overall code structure would be

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'   this routine will ..
'   - unhide all sheets if cell C2 in the Index sheet is clicked
'   - hide relevant sheets if cell C4 in the Index sheet is clicked
    If Selection.Address(0, 0) = "C2" Then
        Call Unhide_All_Sheets
    ElseIf Selection.Address(0, 0) = "C4" Then
        Call Hide_Selected_Sheets
    End If
End Sub

BTW, thanks for updating your profile.

 

[stuartgb100]

Code I have is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'   this routine will unhide all sheets if cell C2 in the Index sheet is single-clicked
'   or hide selected sheets if cell C5 in the Index sheet is single-clicked

    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("C2")) Is Nothing Then
            Call Unhide_All_Sheets
        ElseIf Not Intersect(Target, Range("C5")) Is Nothing Then
            Call Hide_Selected_Sheets
        End If
    End If
End Sub

 

[Peter_SSs]

Code I have is as follows:

Yes, I suspected you may have followed a similar style to your previous code. There are two very minor points with that code

If you happened to decide to select a very large range, for example the whole sheet by clicking the box at the top left of the sheet where the row and column labels intersect to say change the font for the whole worksheet or to change all row heights etc, then the code will error. That possibility would easily be overcome by using this line instead of your similar one

If Selection.CountLarge = 1 Then

The second is simply that you can do in one short line what your code takes two lines to do.

 

[stuartgb100]

Understood, thanks.

Something rather strange is happening .....

Say the normally visible sheets are Index, Vis1, Vis2, Vis3 and Vis4.
So, I work on the Index sheet and it is the activesheet when I save & close the workbook.
When I reopen it - all is good.
However, if I work on any of the Vis sheets, and it is active when I save and close - it doesn't work.
When I reopen the workbook, that particular workbook also has the list of hyperlink sheet names in col A !

Hmmmm... so I thought that maybe my code was not finding the Index sheet.
So I tested that as follows:
The list of hyperlinks occupies 27 rows, so I placed 'Test' in A30 of the index sheet, clicked on Vis3, then saved & closed the workbook.
When I reopened it, Vis 3 had the list of hyperlinks in col A.
The index sheet also had the list, but A30 was empty/blank.

Here's the code:

Private Sub Workbook_Open()

'This routine does as follows:
'   clears all data from col A in the 'Index'
'   adds the value "Index of Sheets (Hyperlinks)" to cell "A1" on the "Index" sheet
'   creates the required list of hyperlinks, starting in cell "A3"
'   note that required is defined as the sheet tabs which I want visible

    Dim ws As Worksheet, i As Integer
    
    Application.DisplayAlerts = False
       
    Worksheets("Index").Columns(1).ClearContents
    Worksheets("Index").Range("A1").Value = "Index of Sheets (Hyperlinks)"
    i = 2
    For Each ws In ThisWorkbook.Worksheets
'   create the list of hyperlinks
        If ws.Name <> "Index" Then
            i = i + 1
            Sheets("Index").Range("A" & i).Value = ws.Name
            Sheets("Index").Hyperlinks.Add Anchor:=Range("A" & i), Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
        End If
    Next ws
    
 
'   now make visible only the required sheets
    Const AlwaysVisible As String = "|Index|Urgent|Shopping|Today|Tomorrow|This Week|This Month|Appointments etc|Ref|" '<- Amend if/as required
 
    For Each ws In Worksheets
      ws.Visible = InStr(1, AlwaysVisible, "|" & ws.Name & "|") > 0
    Next ws
 
    Sheets("Index").Columns("A").AutoFit
    Application.DisplayAlerts = True
    
End Sub

Can you explain what is happening please ?

 

[stuartgb100]

Just to say that I know that I could use the WorkbookClose event to make the Index sheet active on closing, but I really wanted to understand what is going on.
Thanks.