TOC unhide sheet

josros60

Active Member
Joined
Jun 27, 2010
Messages
499
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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

Active Member
Joined
Jun 27, 2010
Messages
499
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_

Board Regular
Joined
Apr 28, 2010
Messages
167
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?
 
Last edited:

josros60

Active Member
Joined
Jun 27, 2010
Messages
499
back button is macro on each sheet to go back TOC Contents.
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
521
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

Active Member
Joined
Jun 27, 2010
Messages
499
Can you please upload the code for unhide sheet TOC

Thanks
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
521
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,026
Messages
5,484,258
Members
407,436
Latest member
Szafranski

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top