Macro-hide sub sheets?

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

--
Regards,
Tom Ogilvy

"CLR" <> wrote in message
news:...
> 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
>
>

 

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

>-----Original Message-----
>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
>
>
>.
>

 

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




"Tom Ogilvy" <> wrote in message
news:#...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "CLR" <> wrote in message
> news:...
> > 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
> >
> >
>
>

 

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



<> wrote in message
news:2f0901c4c272$8e0db5e0$...
> 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
>
> >-----Original Message-----
> >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
> >
> >
> >.
> >

 

You need a period before Hidden, i.e.

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

On Thu, 4 Nov 2004 20:05:39 -0500, "CLR" <> wrote:

>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
>
>
>
><> wrote in message
>news:2f0901c4c272$8e0db5e0$...
>> 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
>>
>> >-----Original Message-----
>> >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
>> >
>> >
>> >.
>> >
>

 

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

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


On Thu, 4 Nov 2004 19:42:11 -0500, "CLR" <> wrote:

>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
>
>
>
>
>"Tom Ogilvy" <> wrote in message
>news:#...
>> 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
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "CLR" <> wrote in message
>> news:...
>> > 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
>> >
>> >
>>
>>
>

 

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



"Myrna Larson" <> wrote in message
news:...
> You need a period before Hidden, i.e.
>
> if sheets("sheets1").hidden = true then
>
> On Thu, 4 Nov 2004 20:05:39 -0500, "CLR" <> wrote:
>
> >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
> >
> >
> >
> ><> wrote in message
> >news:2f0901c4c272$8e0db5e0$...
> >> 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
> >>
> >> >-----Original Message-----
> >> >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
> >> >
> >> >
> >> >.
> >> >
> >
>