code to unhide tabs based on cell contents
If you do want just a non-event macro assigned to a button try
this.........first delete the worksheet module event code then paste
this macro into a general module. Assign it to a button on Contents
sheet.
Sub unhide()
Dim wsh As Worksheet
Dim rng As Range
Set rng = Sheets("Contents").Range("E4")
Sheets(rng.Value).Visible = True
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name < rng.Value Then wsh.Visible = xlSheetHidden
Next wsh
Sheets("Contents").Visible = True
End Sub
Select from DV list or type a name in E4 then hit your button to run
the macro.
Gord
On Thu, 25 Aug 2011 13:37:48 -0700, Gord wrote:
Delete double-click code and use this change code.
Enter a sheet name in E4 to unhide that sheet.
I would use a data validation dropdown list in E4 on Contents sheet
and choose from that list.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsh As Worksheet
Const WS_RANGE As String = "E4"
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Sheets(Target.Value)
.Visible = True
.Select
End With
End If
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name < Target.Value Then wsh.Visible = xlSheetHidden
Next wsh
Sheets("Contents").Visible = True
Cancel = True
ws_exit:
Application.EnableEvents = True
End Sub
Gord
On Thu, 25 Aug 2011 13:02:09 -0700 (PDT), pat67
wrote:
What about what i said in my second part? If i just want use the
contents of a single cell as the named sheet to open? In other words,
in cell E4 say they would enter NC-41282 and then i have a button that
says "Edit". when they click the button, the NC-41282 tab is opened.
|