On Aug 25, 3:36*pm, Gord wrote:
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- Hide quoted text -

- Show quoted text -

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.