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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. lothario

    How do you call one Sub from another Sub ?

    lothario, Oct 17, 2003, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    466
    lothario
    Oct 17, 2003
  2. Hari

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

    Hari, Jun 10, 2004, in forum: Microsoft Excel Programming
    Replies:
    17
    Views:
    4,398
    Jamie Collins
    Jun 15, 2004
  3. StargateFanFromWork
    Replies:
    6
    Views:
    663
    StargateFanFromWork
    Jan 26, 2006
  4. Punsterr
    Replies:
    3
    Views:
    448
    Punsterr
    Feb 21, 2006
  5. Hide all sheets but selected sheets - an example

    , Apr 7, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    325
    Guest
    Apr 7, 2006
  6. Guest

    how to hide a macro name from macro listings

    Guest, May 10, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    299
    Guest
    May 10, 2006
  7. Corey
    Replies:
    2
    Views:
    822
    Corey
    Dec 11, 2006
  8. Mike Rogers

    Sub, Private Sub, Public Sub

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