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

Macro-hide sub sheets?
 

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

Macro-hide sub sheets?
 

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


.

Macro-hide sub sheets?
 

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

Macro-hide sub sheets?
 

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


.

Macro-hide sub sheets?
 

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


.

Macro-hide sub sheets?
 

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

Macro-hide sub sheets?
 

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


.