Macro-hide sub sheets?

[Guest]

Hi All.....

I create various maps, or diagrams in Excel. There are two major types,
"ORIGINAL", and "FUTURE". Each type has several support sheets to go with
it. I was wondering if it would be possible to create a macro that would
automatically expose to view, only those sheets (sheets C-H)associated with
the ORIGINAL sheet when I click on that one, and then hide those and expose
only those (sheets I-N) associated with the FUTURE sheet when I click on that
one, and back and forth.

TIA for any ideas.....

Vaya con DIos,
Chuck, CABGx3

 

[Tom Ogilvy]

this would be a start

Put this in the ThisWorkbook Module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim b1 As Long, b2 As Long
Dim sht As Worksheet, sChr As String
Select Case LCase(Sh.Name)
Case "original"
b1 = xlSheetVisible
b2 = xlSheetHidden
Case "future"
b1 = xlSheetHidden
b2 = xlSheetVisible
Case Else
Exit Sub
End Select
For Each sht In Worksheets
sChr = UCase(sht.Name)
If Len(sChr) = 1 Then
If sChr >= "C" And sChr <= "H" Then
sht.Visible = b1
ElseIf sChr >= "I" And sChr <= "N" Then
sht.Visible = b2
End If
End If
Next
End Sub

 

[Guest]

hi,
maybe not clickin on the sheet but you could add a custom
icon and assign a macro to it.
Sub machidesheets()
if sheets("sheets1")hidden = true then
Sheets("sheet1").unhide
Sheets("sheet2").hide
else
Sheets("sheet1").hide
Sheets("sheet2").unhide
end if
end sub
this code would unhide sheet1 and hide sheet2 if sheet1
was hidden or unhide sheet 2 and hide sheet1 if sheet1 was
not hidden.
you would have to add a line for each sheet you want to
hide or unhide.
regards

 

[CLR]

Thanks a zillion Tom,..........as usual, your code works absolutely
perfect!!!

One additonal thing, if you please..............how could it be modified to
allow the 12 support sheets (now called C to H, and I to N) to have unique
text names instead of being "six letters in a row"?

Thanks again,
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks very much for your response, I could also use something like this,
but I cannot seem to get it to work. I keep getting an error on the first
line.........and it comes up RED when viewing the code...........

if sheets("sheets1")hidden = true then

Vaya con Dios,
Chuck, CABGx3

 

[Myrna Larson]

You need a period before Hidden, i.e.

if sheets("sheets1").hidden = true then

 

[Myrna Larson]

Try another Select Case block, similar to the first one. Note that you can
write something like

Case "ABC", "JKL", "Sheet2", "Sheet1"

 

[CLR]

Thanks Myrna...........I tried that already and it changed that line of code
form RED to BLACK, but I still couldn't get it to run. I tried changing the
sheet names also, still no luck.

Vaya con Dios,
Chuck, CABGx3