Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using cell contents to link to other tabs | Excel Worksheet Functions | |||
Code to hide tabs based on input | Excel Discussion (Misc queries) | |||
Have code check cell contents on edit. | Excel Worksheet Functions | |||
Renaming tabs from particular cell contents | Excel Worksheet Functions | |||
Hide or unhide sheets based on cell | Excel Discussion (Misc queries) |