|
Hiding Tabs
I am trying to find a way to hide or show tabs based answers from a
user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. |
Hiding Tabs
what do you mean by tabs? do you mean the worksheet tabs at the bottom?
"newguy" wrote: I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. |
Hiding Tabs
This is the correct place since you need a macro. Right click sheet tabview codeinsert this. Change $A$8 and sheet name to suit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$8" Then Exit Sub If UCase(Target) = "YES" Then Sheets("sheet2").Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. |
Hiding Tabs
On Sep 16, 9:22*am, Wullie wrote:
what do you mean by tabs? do you mean the worksheet tabs at the bottom? "newguy" wrote: I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. Yes |
Hiding Tabs
On Sep 16, 9:26*am, "Don Guillett" wrote:
This is the correct place since you need a macro. Right click sheet tabview codeinsert this. Change $A$8 and sheet name to suit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$8" Then Exit Sub If UCase(Target) = "YES" Then Sheets("sheet2").Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. 2 Questions will the changes on what tabs show change as the user inputs the answers to the questions? And to duplicate this for other tabs I just need to copy the "If statements" and change the Cell reference and tab name to suit? Thank you |
Hiding Tabs
You can't hide the tabs individually... either they all are visible or they
all are not visible. To hide all the tabs, execute this statement... ActiveWindow.DisplayWorkbookTabs = False to redisplay them again, execute this statement... ActiveWindow.DisplayWorkbookTabs = True -- Rick (MVP - Excel) "newguy" wrote in message ... On Sep 16, 9:22 am, Wullie wrote: what do you mean by tabs? do you mean the worksheet tabs at the bottom? "newguy" wrote: I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. Yes |
Hiding Tabs
If I can't hide tabs individually can I add tabs individually based on a template? My original plan was to have a 10 or so tabs and 10 questions based on their answers (Yes/No) they would either show up or be hidden. Can I instead have the worksheets saved as templates and based on their answers to the questions add those templates as tabs? |
Hiding Tabs
Based on the wording of your latest post, I think I need a clarification
please. Perhaps you and I are using the word "tabs" differently... the tabs I was talking about in my response is the small extension located at the bottom of a worksheet that contains the name of the worksheet... your last response makes me think you might be talking about the entire worksheet. Exactly what do you want to be hidden... the worksheet's identifying tab at the bottom of the worksheet or the entire worksheet itself? -- Rick (MVP - Excel) "newguy" wrote in message ... If I can't hide tabs individually can I add tabs individually based on a template? My original plan was to have a 10 or so tabs and 10 questions based on their answers (Yes/No) they would either show up or be hidden. Can I instead have the worksheets saved as templates and based on their answers to the questions add those templates as tabs? |
Hiding Tabs
On Sep 16, 10:17*am, "Rick Rothstein"
wrote: Based on the wording of your latest post, I think I need a clarification please. Perhaps you and I are using the word "tabs" differently... the tabs I was talking about in my response is the small extension located at the bottom of a worksheet that contains the name of the worksheet... your last response makes me think you might be talking about the entire worksheet. Exactly what do you want to be hidden... the worksheet's identifying tab at the bottom of the worksheet or the entire worksheet itself? -- Rick (MVP - Excel) "newguy" wrote in message ... If I can't hide tabs individually can I add tabs individually based on a template? *My original plan was to have a 10 or so tabs and 10 questions based on their answers (Yes/No) they would either show up or be hidden. Can I instead have the worksheets saved as templates and based on their answers to the questions add those templates as tabs? Sorry about the confusion the entire worksheet is what I want to be hidden. |
Hiding Tabs
In that case... stick with the sub-thread Don started with you... his answer
is the one you are looking to implement. -- Rick (MVP - Excel) "newguy" wrote in message ... On Sep 16, 10:17 am, "Rick Rothstein" wrote: Based on the wording of your latest post, I think I need a clarification please. Perhaps you and I are using the word "tabs" differently... the tabs I was talking about in my response is the small extension located at the bottom of a worksheet that contains the name of the worksheet... your last response makes me think you might be talking about the entire worksheet. Exactly what do you want to be hidden... the worksheet's identifying tab at the bottom of the worksheet or the entire worksheet itself? -- Rick (MVP - Excel) "newguy" wrote in message ... If I can't hide tabs individually can I add tabs individually based on a template? My original plan was to have a 10 or so tabs and 10 questions based on their answers (Yes/No) they would either show up or be hidden. Can I instead have the worksheets saved as templates and based on their answers to the questions add those templates as tabs? Sorry about the confusion the entire worksheet is what I want to be hidden. |
Hiding Tabs
So this will look at a range of cells and show the worksheets based on
what values is in that range of cells? This seems a lot more complex than your original solution and I really don't understand what it is doing. On Sep 16, 10:33*am, "Don Guillett" wrote: You can restrict the range to just those cells desired. You could use a select case for each of the tabs desired. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a2:a8")) Is Nothing Then Exit Sub On Error Resume Next 'hide all but menu For Each sh In Sheets * If sh.Name < "Menu" Then sh.Visible = False Next sh '------ Select Case Target.Row *Case 2: x = "sheet2" *Case 3: x = "sheet3" 'etc *Case Else End Select If UCase(Target) = "YES" Then Sheets(x).Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... On Sep 16, 9:26 am, "Don Guillett" wrote: This is the correct place since you need a macro. Right click sheet tabview codeinsert this. Change $A$8 and sheet name to suit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$8" Then Exit Sub If UCase(Target) = "YES" Then Sheets("sheet2").Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message .... I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. 2 Questions will the changes on what tabs show change as the user inputs the answers to the questions? And to duplicate this for other tabs I just need to copy the "If statements" and change the Cell reference and tab name to suit? Thank you |
Hiding Tabs
No, this will look in the range for yes in a cell. As written, it hides all
but the menu sheet and if yes in a cell it will UNhide the sheet for that cell. If all else fails, send your workbook to my address below along with EXACTLY what you want and I'll take a look. -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... So this will look at a range of cells and show the worksheets based on what values is in that range of cells? This seems a lot more complex than your original solution and I really don't understand what it is doing. On Sep 16, 10:33 am, "Don Guillett" wrote: You can restrict the range to just those cells desired. You could use a select case for each of the tabs desired. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a2:a8")) Is Nothing Then Exit Sub On Error Resume Next 'hide all but menu For Each sh In Sheets If sh.Name < "Menu" Then sh.Visible = False Next sh '------ Select Case Target.Row Case 2: x = "sheet2" Case 3: x = "sheet3" 'etc Case Else End Select If UCase(Target) = "YES" Then Sheets(x).Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... On Sep 16, 9:26 am, "Don Guillett" wrote: This is the correct place since you need a macro. Right click sheet tabview codeinsert this. Change $A$8 and sheet name to suit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$8" Then Exit Sub If UCase(Target) = "YES" Then Sheets("sheet2").Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. 2 Questions will the changes on what tabs show change as the user inputs the answers to the questions? And to duplicate this for other tabs I just need to copy the "If statements" and change the Cell reference and tab name to suit? Thank you |
Hiding Tabs
That works except one aspect if I answer more than one of the
questions yes it still only displays one additional tab. So I have 10 questions that the users are asked, if they answer yes to all of them I need all 10 corresponding tabs. If the answer yes to only 5 I need those 5 corresponding tabs. Right now if I answer yes to the first question it gives me that tab, then if I answer the next question it replaces that tab with the one I need for the last question that I just answered yes to. Sorry if that is confusing if it would be easier I can send you what little I have of my workbook in progress. Thanks Again On Sep 16, 11:45*am, "Don Guillett" wrote: No, this will look in the range for yes in a cell. As written, it hides all but the menu sheet and if yes in a cell it will UNhide the sheet for that cell. If all else fails, send your workbook to my address below along with EXACTLY what you want and I'll take a look. -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message ... So this will look at a range of cells and show the worksheets based on what values is in that range of cells? *This seems a lot more complex than your original solution and I really don't understand what it is doing. On Sep 16, 10:33 am, "Don Guillett" wrote: You can restrict the range to just those cells desired. You could use a select case for each of the tabs desired. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("a2:a8")) Is Nothing Then Exit Sub On Error Resume Next 'hide all but menu For Each sh In Sheets If sh.Name < "Menu" Then sh.Visible = False Next sh '------ Select Case Target.Row Case 2: x = "sheet2" Case 3: x = "sheet3" 'etc Case Else End Select If UCase(Target) = "YES" Then Sheets(x).Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message .... On Sep 16, 9:26 am, "Don Guillett" wrote: This is the correct place since you need a macro. Right click sheet tabview codeinsert this. Change $A$8 and sheet name to suit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$8" Then Exit Sub If UCase(Target) = "YES" Then Sheets("sheet2").Visible = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "newguy" wrote in message .... I am trying to find a way to hide or show tabs based answers from a user on a menu page. So if the user answers "yes" to the first question then tab 1 shows up if "no" tab 1 does not show up. Haven't had a lot of experience with VBA thank you for your help. 2 Questions will the changes on what tabs show change as the user inputs the answers to the questions? And to duplicate this for other tabs I just need to copy the "If statements" and change the Cell reference and tab name to suit? Thank you |
Hiding Tabs
|
| All times are GMT +1. The time now is 05:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com