Creating multiple tabs within tabs

Alright so I have an excel program that consists of roughly 40 pages, a lot are alike with minor differences and it would be nice to combine them and make it look cleaner and easier to navigate for my fellow coworkers. 

........

........

Hi Mackenzie,

what about creating a worksheet "Table of contents "which contains hyperlinks to all sheets? For example, you can place hyperlinks to all 1-st level sheets in Column A, 2nd level sheets in Column B and so on. If a worksheet has some related child sheets, you can place hyperlinks to them at the top of this sheet. It makes navigation much easier.

Also you can create an additional custom tab/group in the Excel ribbon and add Back and Forward button and that helps you to track your navigation route.

This reflects what you have stated in your shared workbook.

First, you need a sheet that is not in your list that will be the activesheet when the workbook is opened.   Name this sheet Main.

Put this code in the thisworkbook module. 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim v As Variant, vv(1 To 5) As Variant

v = Array("Input", "Commission", "PO")

vv(1) = Array("INPUT1", "INPUT2", "INPUT3", "INPUT4")  ''vInput =

vv(2) = Array("COMMISSION1", "COMMISSION2", "COMMISSION3", "COMMISSION4", "COMMISSION5") ''vComm =

vv(3) = Array("ESTIMATE", "FINAL")   ''vPO =

vv(4) = Array("ESTIMATE1", "ESTIMATE2", "ESTIMATE3")  ''vEstimate

vv(5) = Array("FINAL1", "FINAL2", "FINAL3")  ''vFinal

Select Case UCase(Sh.Name)

   Case "MAIN"

      For i = LBound(v) To UBound(v)

       Worksheets(v(i)).Visible = xlSheetVisible

      Next i

      For i = 1 To 5

          For j = LBound(vv(i)) To UBound(vv(i))

             Worksheets(vv(i)(j)).Visible = xlSheetHidden

          Next j

      Next i

   Case "INPUT"

      For i = 1 To 5

          For j = LBound(vv(i)) To UBound(vv(i))

           If i = 1 Then

             Worksheets(vv(i)(j)).Visible = xlSheetVisible

           Else

             Worksheets(vv(i)(j)).Visible = xlSheetHidden

           End If

          Next j

      Next i

   Case "COMMISSION"

      For i = 1 To 5

          For j = LBound(vv(i)) To UBound(vv(i))

           If i = 2 Then

             Worksheets(vv(i)(j)).Visible = xlSheetVisible

           Else

             Worksheets(vv(i)(j)).Visible = xlSheetHidden

           End If

          Next j

      Next i

   Case "PO"

      For i = 1 To 5

          For j = LBound(vv(i)) To UBound(vv(i))

           If i = 3 Then

             Worksheets(vv(i)(j)).Visible = xlSheetVisible

           Else

             Worksheets(vv(i)(j)).Visible = xlSheetHidden

           End If

          Next j

      Next i

   Case "ESTIMATE"

      For i = 1 To 5

          For j = LBound(vv(i)) To UBound(vv(i))

           If i = 4 Or i = 3 Then

             Worksheets(vv(i)(j)).Visible = xlSheetVisible

           Else

             Worksheets(vv(i)(j)).Visible = xlSheetHidden

           End If

          Next j

      Next i

   Case "FINAL"

      For i = 1 To 5

          For j = LBound(vv(i)) To UBound(vv(i))

           If i = 5 Or i = 3 Then

             Worksheets(vv(i)(j)).Visible = xlSheetVisible

           Else

             Worksheets(vv(i)(j)).Visible = xlSheetHidden

           End If

          Next j

      Next i

End Select

End Sub

Now select any other sheet (even if you need to create a new sheet).  Then select main.  

Now save your workbook

if you  created an additional sheet, you can delete that sheet.  

Select Main anytime you want to reset to the 4 sheets   Main  Input Commission  PO

Now select tabs as you wish.

--

Regards,

Tom Ogilvy

Hi Tom,

Mackenzie wants fours sheets (INPUT, COMMISSION, PO, INVOICE) to be visible. When users clicks on any one of these associated sheets should be visible and all others (except the four and the sheets associated with the current selection) be hidden.

Hi Mackenzie,

You can write the required code within each sheet's activation code to show what you want and hide everything else.

Code for INPUT sheet is given below

In VB Editor click on the INPUT sheet and paste the code below

Private Sub Worksheet_Activate()

Dim v As Variant, i As Long

' List of sheets to be shown when INPUT is activated

v = Array("INPUT", "COMMISSION", "PO", "INVOICE", "INPUT1", "INPUT2", "INPUT3", "INPUT4", "INPUT5")

Worksheets("INPUT").Visible = xlSheetVisible

' Hide all except INPUT

For Each ws In Worksheets

   If ws.Name <> "INPUT" Then

    ws.Visible = xlHidden

   End If

Next

' Show the sheets you want to show based on list in v

For i = LBound(v) To UBound(v)

   Worksheets(v(i)).Visible = xlSheetVisible

Next

End Sub

During testing use the code below (in a standard module) to unhide all sheets

Sub showAll()

    For Each ws In Worksheets

        ws.Visible = xlSheetVisible

    Next

End Sub

Mackenzie, Luckenbill,

basic code would be something like

Private Sub cmdInput_Click()

Dim v as Variant, i as long

v = Array("INPUT1", "INPUT2", "INPUT3", "INPUT4", "INPUT5")

for i = lbound(v) to ubound(v)

   worksheets(v(i)).Visible = xlSheetVisible

Next

End sub

That assumes a command button named cmdInput

You have only said you want the listed sheets made visible.  You didn't say what happens to other sheets. 

If you want all hidden then assume you have a sheet named master that has your button and will always be visible (you can never hide all sheets - it will raise an error)

Private Sub cmdInput_Click()

Dim sh as worksheet, i as long, v as variant

' hide all sheets but master

for each sh in worksheets

  if Ucase(sh.name) <> "MASTER" then

     sh.Visible = xlSheetHidden

 end if

Next

' now show the "input" sheets

v = Array("INPUT1", "INPUT2", "INPUT3", "INPUT4", "INPUT5")

for i = lbound(v) to ubound(v)

   worksheets(v(i)).Visible = xlSheetVisible

Next

End sub

You would have similar code for your other groups.

--

Regards,

Tom Ogilvy

What would the macro be though? I am not sure what to type into the macro to get it to perform this function.