Macro-hide sub sheets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
Click to expand...

Vaya con Dios,
Chuck, CABGx3
 
Try another Select Case block, similar to the first one. Note that you can
write something like

Case "ABC", "JKL", "Sheet2", "Sheet1"
 
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
 
Back
Top