You don't say, but I will assume when a new sheet is unhidden, the
current activesheet is hidden.

i.e. only one sheet plus Contents sheet open at any time.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim wsh As Worksheet
Const WS_RANGE As String = "B4:AE4" '30 sheets edit to suit
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

This is sheet event code. Assumes you have a sheet named "Contents".
Right-click on "Content" tab and select "View Code"

Copy/paste into that module.

Alt + q to return to Excel...........double-click on a cell within
ws_range


Gord Dibben Microsoft Excel MVP


On Thu, 25 Aug 2011 11:03:51 -0700 (PDT), pat67
wrote:

Hi, I have a large file with 30 some tabs. what i want to do is hide
all the tabs except a contents tab and be able to unhide specific tabs
based on which cell someone clicks in. I can do it by putting in this

Sheets("NC-41282").Visible = True
Sheets("NC-41282").Select


But i have to do that for each NC-Number. what I am looking for is to
do it so the vba knows whic sheet to open based on which cell is
clicked. So cell B4 for example shows NC-41283. I wanted so when that
cell is clicked the tab NC-41283 opens. C4 would be NC-41284 and so
on. I am obviously not really adept at vba so any help would be
appreciated. Thanks