Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Hi all,
Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets hidden until they are listed in that range, then UNHIDE upon running the macro. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Sure, there are a couple of ways. You could put the list of names into an
Array and loop through all your worksheets to Match their names against the Array List. Or you could do a double loop by looping through each list name and comparing it to all the sheet names by looping through them. Or you could just loop through the worksheets and compare their names with a Match to the range on sheet "Main". Mike F "Tom" wrote in message ... Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets hidden until they are listed in that range, then UNHIDE upon running the macro. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
You can't have all your sheets hidden, at least one must be visible. If Main
was always visible, you could always use even t code, like so Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom" wrote in message ... Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets hidden until they are listed in that range, then UNHIDE upon running the macro. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
I guess I should have said that my MAIN sheet would always be visible - all
others would be hidden. Thanks for the code Bob, I'll give it a try! "Bob Phillips" wrote: You can't have all your sheets hidden, at least one must be visible. If Main was always visible, you could always use even t code, like so Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom" wrote in message ... Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets hidden until they are listed in that range, then UNHIDE upon running the macro. Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
unhide sheets
Hi Bob,
This code works...however, I have to DELETE the contents of the cells (C8:C17) before the sheets become visible. Is there a way around that? I would like the list to remain intact and not delete the list until I need to so a VLOOKUP formula that I have linked to those cells can work. Thanks! Tom "Bob Phillips" wrote: You can't have all your sheets hidden, at least one must be visible. If Main was always visible, you could always use even t code, like so Option Explicit Private Const WS_RANGE As String = "C8:C17" '<=== change to suit Private prevVal As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value = "" Then If prevVal < "" Then Worksheets(prevVal).Visible = True End If Else Worksheets(.Value).Visible = False End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then prevVal = Target.Value End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tom" wrote in message ... Hi all, Is it possible to have a list of sheet names in C8:C17 on sheet "Main", and have a macro that will UNHIDE those sheets that are listed in that range? Essentially, I want to have all my sheets hidden until they are listed in that range, then UNHIDE upon running the macro. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help unhide sheets | Excel Programming | |||
Unhide sheets | Excel Programming | |||
How to Unhide sheets | Excel Discussion (Misc queries) | |||
Add sheets that are unhide | Excel Programming | |||
Add sheets that are unhide | Excel Programming |