Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 25th 11, 07:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 48
Default 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

  #2   Report Post  
Old August 25th 11, 07:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 48
Default 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
  #3   Report Post  
Old August 25th 11, 08:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default 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

  #4   Report Post  
Old August 25th 11, 08:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 86
Default 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

  #5   Report Post  
Old August 25th 11, 09:02 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 48
Default 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.


  #6   Report Post  
Old August 25th 11, 09:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 86
Default 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.

  #7   Report Post  
Old August 25th 11, 10:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 86
Default 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.

  #8   Report Post  
Old August 26th 11, 12:43 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2011
Posts: 587
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
using cell contents to link to other tabs CLGonline Excel Worksheet Functions 2 June 3rd 09 07:31 AM
Code to hide tabs based on input newguy Excel Discussion (Misc queries) 2 September 16th 08 02:20 PM
Have code check cell contents on edit. Andrew Excel Worksheet Functions 1 January 16th 08 04:47 AM
Renaming tabs from particular cell contents scotty Excel Worksheet Functions 5 December 11th 07 05:44 PM
Hide or unhide sheets based on cell billinr Excel Discussion (Misc queries) 2 July 13th 07 07:42 PM


All times are GMT +1. The time now is 11:34 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017