unhide sheets

[Tom]

Hi all,
I have a list in C8:C17 that lists the names of sheets that I would like to
have a macro that UNHIDES the sheets that are listed. Bob Phillips provided
me with this code that works, however, it has a drawback in that after I
enter in the name of the sheet, I have to actually DELETE the contents of
that cell before the macro will run. The sheets that I am UNHIDING have
VLOOKUP formulas on them that reference otheR cells on that main sheet so the
sheet name needs to stay visible for VLOOKUP to have a reference.
Can someone modify this so that the macro will run without having to delete
the cell contents.

Thanks!

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

 

[Tom Ogilvy]

Private Const WS_RANGE As String = "C8:C17" '<=== change to suit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sh as worksheet
If Target.count > 1 then exit sub
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
set sh = nothing
on error resume next
set sh = worksheets(Target.value)
On error goto 0
if sh.Visible <> xlsheetVisible then
sh.Visible = xlSheetVisible
Else
sh.visible = xlSheetHidden
end if
End If
End Sub

since I don't know what question you asked that caused Bob to write that
code, it is hard to say how to change it (in otherwords, I don't know what
functionality you asked for). In any event, if you select a cell in
WS_Range, if it has a worksheet name in it, then if that sheet is visible, it
is hidden and if it is hidden it is made visible.

If this is what you want
Remove the existing SelectionChange and Change events and copy in this code.
Perhaps test it in a copy of your workbook to see if that is what you want.

 

[Tom]

Hi Tom,
Thanks for the reply, here is the original problem:
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 (except sheet "Main") hidden until
they are listed in that range, then UNHIDE upon running the macro.

As I mentioned, the code provided does work...but only upon deleting the
cells contents which doesn't allow my VLOOKUP formula to have a reference. I
need the list to remain in C8:C17 for the VLOOKUP.

Thanks,
Tom

 

[Tom Ogilvy]

Remove all events in the sheet (Main) with the range C8:C17 and paste in
this one event.

Test this on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, sh1 As Worksheet
Dim rng As Range, cell As Range
Set rng = Worksheets("Main").Range("C8:C17")
If Not Intersect(Target, rng) Is Nothing Then
For Each sh1 In Worksheets
bVisible = False
If LCase(sh1.Name) <> "main" Then
For Each cell In rng
Set sh = Nothing
On Error Resume Next
Set sh = Worksheets(cell.Value)
On Error GoTo 0
If Not sh Is Nothing Then
If sh.Name = sh1.Name Then
bVisible = True
Exit For
End If
End If
Next cell
If bVisible Then
sh1.Visible = xlSheetVisible
Else
sh1.Visible = xlSheetHidden
End If
End If
Next sh1
End If
End Sub

worked for me.

 

[Tom]

Absolutely perfect!

Thanks again for the assistance.