Macro-hide sub sheets?

Discussion in 'Microsoft Excel Programming' started by Guest, Nov 4, 2004.

  1. Guest

    Guest 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
     
    Guest, Nov 4, 2004
    #1
    1. Advertisements

  2. Guest

    Tom Ogilvy Guest

    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
    >
    >
     
    Tom Ogilvy, Nov 4, 2004
    #2
    1. Advertisements

  3. Guest

    Guest 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

    >-----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
    >
    >
    >.
    >
     
    Guest, Nov 4, 2004
    #3
  4. Guest

    CLR Guest

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

    >
    >
     
    CLR, Nov 5, 2004
    #4
  5. Guest

    CLR Guest

    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
    > >
    > >
    > >.
    > >
     
    CLR, Nov 5, 2004
    #5
  6. Guest

    Myrna Larson Guest

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

    >
     
    Myrna Larson, Nov 5, 2004
    #6
  7. Guest

    Myrna Larson Guest

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

    >>
    >>

    >
     
    Myrna Larson, Nov 5, 2004
    #7
  8. Guest

    CLR Guest

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

    > >

    >
     
    CLR, Nov 6, 2004
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Hari

    Macro, Module, function, sub and This workbook and sheets ?

    Hari, Jun 10, 2004, in forum: Microsoft Excel Programming
    Replies:
    17
    Views:
    2,669
    Jamie Collins
    Jun 15, 2004
  2. StargateFanFromWork
    Replies:
    6
    Views:
    416
    StargateFanFromWork
    Jan 26, 2006
  3. Hide all sheets but selected sheets - an example

    , Apr 7, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    224
    Guest
    Apr 7, 2006
  4. Corey
    Replies:
    2
    Views:
    553
    Corey
    Dec 11, 2006
  5. Mike Rogers

    Sub, Private Sub, Public Sub

    Mike Rogers, Apr 15, 2010, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    30,072
    Mike Rogers
    Apr 16, 2010
Loading...

Share This Page