TOC unhide sheet
Results 1 to 9 of 9

Thread: TOC unhide sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    404
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default TOC unhide sheet

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TOC unhide sheet

    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?

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    404
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TOC unhide sheet

    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

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TOC unhide sheet

    Quote Originally Posted by josros60 View Post
    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 by Steve_; Jul 9th, 2019 at 04:16 PM.

  5. #5
    Board Regular
    Join Date
    Jun 2010
    Posts
    404
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TOC unhide sheet

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

  6. #6
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    521
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: TOC unhide sheet

    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.

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Jun 2010
    Posts
    404
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TOC unhide sheet

    Can you please upload the code for unhide sheet TOC

    Thanks

  8. #8
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    521
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: TOC unhide sheet

    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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  9. #9
    Board Regular
    Join Date
    Nov 2013
    Location
    Hampshire, UK
    Posts
    521
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: TOC unhide sheet

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

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •