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

On Aug 25, 2:03*pm, pat67 <pbus...@comcast.net> 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


Or better yet. Assign the code to a button that takes the info from
say cell B4 and says open whatever tab is in cell B4. that way they
can enter whichever they want

hi,

i guess the "tab's names" are in cells A4: AD4
you could use "Worksheet_SelectionChange" event to execute this task.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set isect = Application.Intersect(Target, Range("A4:AD4"))
If Not isect Is Nothing Then
Sheets(Target).Visible = True
Sheets(Target).Select
End If
End Sub


--
isabelle


Le 2011-08-25 14:03, pat67 a écrit :
> 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

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 <(E-Mail Removed)>
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

On Aug 25, 3:36*pm, Gord <phnor...@shaw.ca> 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 <pbus...@comcast.net>
> 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.

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 <(E-Mail Removed)>
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.

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 <(E-Mail Removed)> 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 <(E-Mail Removed)>
>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.

hi,

Private Sub CommandButton1_Click()
sh = ActiveSheet.Range("E4")
Sheets(sh).Visible = True
Sheets(sh).Select
End Sub

--
isabelle



Le 2011-08-25 16:02, pat67 a écrit :
>
> 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.