code to unhide tabs based on cell contents
 

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

code to unhide tabs based on cell contents
 

On Aug 25, 2:03*pm, 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


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

code to unhide tabs based on cell contents
 

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

code to unhide tabs based on cell contents
 

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

code to unhide tabs based on cell contents
 

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.

code to unhide tabs based on cell contents
 

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.

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.

code to unhide tabs based on cell contents
 

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.