TOC unhide sheet

[josros60]

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:

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

 

[Steve_]

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?

 

[josros60]

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

 

[Steve_]

Sorry my English no that good.

when click the back button to Contents sheet hide again January.

thanks

'Back button'? In excel? Is this a button you have made on a form?

 

[josros60]

back button is macro on each sheet to go back TOC Contents.

 

[cooper645]

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.

 

[josros60]

Can you please upload the code for unhide sheet TOC

Thanks

 

[cooper645]

Insert this into the TOC sheet 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

Sub ReturnButton()
Application.ScreenUpdating = False
Sheets("Contents").Visible = True
ActiveSheet.Visible = False
Sheets("Contents").Select
Cells(1, 1).Select
Application.ScreenUpdating = True
End Sub

 

[cooper645]

work has been so busy I completely forgot. my sincere apologies