PC Review


Reply
Thread Tools Rate Thread

Macro-hide sub sheets?

 
 
=?Utf-8?B?Q0xS?=
Guest
Posts: n/a
 
      4th Nov 2004
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


 
Reply With Quote
 
 
 
 
Tom Ogilvy
Guest
Posts: n/a
 
      4th Nov 2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      4th Nov 2004
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
>
>
>.
>

 
Reply With Quote
 
CLR
Guest
Posts: n/a
 
      5th Nov 2004
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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >

>
>



 
Reply With Quote
 
CLR
Guest
Posts: n/a
 
      5th Nov 2004
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



<(E-Mail Removed)> wrote in message
news:2f0901c4c272$8e0db5e0$(E-Mail Removed)...
> 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
> >
> >
> >.
> >



 
Reply With Quote
 
Myrna Larson
Guest
Posts: n/a
 
      5th Nov 2004
You need a period before Hidden, i.e.

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

On Thu, 4 Nov 2004 20:05:39 -0500, "CLR" <(E-Mail Removed)> 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
>
>
>
><(E-Mail Removed)> wrote in message
>news:2f0901c4c272$8e0db5e0$(E-Mail Removed)...
>> 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
>> >
>> >
>> >.
>> >

>


 
Reply With Quote
 
Myrna Larson
Guest
Posts: n/a
 
      5th Nov 2004
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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>news:#(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> >

>>
>>

>


 
Reply With Quote
 
CLR
Guest
Posts: n/a
 
      6th Nov 2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> You need a period before Hidden, i.e.
>
> if sheets("sheets1").hidden = true then
>
> On Thu, 4 Nov 2004 20:05:39 -0500, "CLR" <(E-Mail Removed)> 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
> >
> >
> >
> ><(E-Mail Removed)> wrote in message
> >news:2f0901c4c272$8e0db5e0$(E-Mail Removed)...
> >> 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
> >> >
> >> >
> >> >.
> >> >

> >

>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Call a Public Sub-routine on a Sub-sub-form Renraf Microsoft Access Form Coding 2 4th Nov 2009 03:45 PM
Can you do a sub-Form in a sub-Form ?? (Nest sub-Forms) MyEmailList@gmail.com Microsoft Access 2 25th Apr 2007 03:34 PM
main form, sub form, sub sub form ...etc =?Utf-8?B?RGF3blRyZWFkZXI=?= Microsoft Access Form Coding 8 5th Nov 2006 05:26 AM
How to create a 'Directory' in Access... or Categories & Sub-Cagegories & Sub-Sub-Categories & etc Will Microsoft Access 3 6th Sep 2005 04:51 AM
Re: Access 2003 Parent_Child relationships in sub and sub sub forms Allen Browne Microsoft Access 0 30th Jul 2004 05:23 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:57 PM.