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!

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!

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!

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!

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!