| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to hide sheets based upon cell value | Excel Discussion (Misc queries) | |||
| macro to hide sheets | Excel Discussion (Misc queries) | |||
| Macro-Hide & Unhide Sheets with condition | Excel Discussion (Misc queries) | |||
| If I have my sheets protected will that prevent me running a macro to hide certain sells? | Excel Worksheet Functions | |||
| macro to hide sheets | Excel Discussion (Misc queries) | |||
Hybrid Mode
