TOC unhide sheet

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
778
Office Version
  1. 365
Hi,

i have this code that I found in the web works great just want to know how to modify it so when I click hyperlink
unhide the sheets and when I click back contents hide the sheet again, thanks.

Code:

Code:
Sub TOC_APCHECKLIST2()

'PURPOSE: Add a Table of Contents worksheets to easily navigate to any tab (multiple columns)
'SOURCE: www.TheSpreadsheetGuru.com


Dim sht As Worksheet
Dim Content_sht As Worksheet
Dim myArray As Variant
Dim x As Long, y As Long, z As Long
Dim shtName1 As String, shtName2 As String
Dim ContentName As String
Dim shtCount As Long
Dim ColumnCount As Variant


'Inputs
  ContentName = "Contents"


'Optimize Code
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False


'Delete Contents Sheet if it already exists
  On Error Resume Next
    Worksheets("Contents").Activate
  On Error GoTo 0


  If ActiveSheet.Name = ContentName Then
    myAnswer = MsgBox("A worksheet named [" & ContentName & _
      "] has already been created, would you like to replace it?", vbYesNo)
    
    'Did user select No or Cancel?
      If myAnswer <> vbYes Then GoTo ExitSub
      
    'Delete old Contents Tab
      Worksheets(ContentName).Delete
  End If


'Count how many Visible sheets there are
  For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible = True Then shtCount = shtCount + 1
  Next sht


'Ask how many columns to have
  ColumnCount = Application.InputBox("You have " & shtCount & _
    " visible worksheets." & vbNewLine & "How many columns " & _
    "would you like to have in your Contents tab?", Type:=2)


'Check if user cancelled
  If TypeName(ColumnCount) = "Boolean" Or ColumnCount < 0 Then GoTo ExitSub


'Create New Contents Sheet
  Worksheets.Add Before:=Worksheets(1)


'Set variable to Contents Sheet and Rename
  Set Content_sht = ActiveSheet
  Content_sht.Name = ContentName
  
'Create Array list with sheet names (excluding Contents)
  ReDim myArray(1 To shtCount)


  For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> ContentName And sht.Visible = True Then
      myArray(x + 1) = sht.Name
      x = x + 1
    End If
    
           
  Next sht
  
'Alphabetize Sheet Names in Array List
  For x = LBound(myArray) To UBound(myArray)
    For y = x To UBound(myArray)
      If UCase(myArray(y)) < UCase(myArray(x)) Then
        shtName1 = myArray(x)
        shtName2 = myArray(y)
        myArray(x) = shtName2
        myArray(y) = shtName1
      End If
     Next y
  Next x


'Create Table of Contents
  x = 1


  For y = 1 To ColumnCount
    For z = 1 To WorksheetFunction.RoundUp(shtCount / ColumnCount, 0)
      If x <= UBound(myArray) Then
        Set sht = Worksheets(myArray(x))
        sht.Activate
        With Content_sht
          .Hyperlinks.Add .Cells(z + 2, 2 * y), "", _
          SubAddress:="'" & sht.Name & "'!A1", _
          TextToDisplay:=sht.Name
        End With
        x = x + 1
      End If
    Next z
  Next y


'Select Content Sheet and clean up a little bit
  Content_sht.Activate
  Content_sht.UsedRange.EntireColumn.AutoFit
  ActiveWindow.DisplayGridlines = False


'Format Contents Sheet Title
  With Content_sht.Range("B1")
    .Value = "Table of Contents"
    .Font.Bold = True
    .Font.Size = 18
  End With


ExitSub:
'Optimize Code
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  
  End Sub

Thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I think I can probably help you..but could you reword ("how to modify it so when I click hyperlink unhide the sheets and when I click back contents hide the sheet again, thanks.")


This statement has left me completely confused. Can you clarify this?
 
Upvote 0
Sorry my English no that good.

what I meant, created already the TOC but woul like to hide them and Contents Sheet have all the hyperlinks.

if all sheets are hidden January to December, I would like when I click for example January sheet link to unhide that sheet and when click the back button to Contents sheet hide again January.

thanks
 
Upvote 0
This looks like John walkenbachs code. I get what your saying, that you only want to see either the TOC (table of contents) or the page you click on from the contents. I used this method in a file at work. I’ll upload the code tomorrow that I use if no one comes along with a solution in the meantime.
 
Upvote 0
Insert this into the TOC sheet code

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    If Target.Column = 3 Or Target.Column = 6 Then 'change number to where hyperlinks are
        Sheets(Target.Value).Visible = xlSheetVisible
        Sheets(Target.Value).Select
    End If
    Sheets("Contents").Visible = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub


Assign this macro for your return buttons

Code:
Sub ReturnButton()
Application.ScreenUpdating = False
Sheets("Contents").Visible = True
ActiveSheet.Visible = False
Sheets("Contents").Select
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub
 
Upvote 0
work has been so busy I completely forgot. my sincere apologies
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top