Log In
Register
Home
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
Hi
I'd like to be able to be able to unhide tabs when the user selects an option. What is the best way to handle this?
Thanks
John
Trusted Members
December 20, 2019
Offline
Something like this should work - needs to be in the main tab module.
You can get a bit more creative with the hiding if you have a lot of sheets - for example have another sub that hides all worksheets by default then unhide the one you want at each case, in that way each case willl only be 2 lines
Personally i tend to prefer actual buttons rather than selecting a cell as i think the worksheet is doing less work - the button performs a specific task rather than a selection change which runs each time a cell is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Debug.Print Target
Select Case Target
Case ""
Worksheets("Option 1").Visible = xlHidden
Worksheets("Option 2").Visible = xlHidden
Worksheets("Option 3").Visible = xlHidden
Case "Option 1"
Worksheets("Option 1").Visible = True
Worksheets("Option 2").Visible = xlHidden
Worksheets("Option 3").Visible = xlHidden
Case "Option 2"
Worksheets("Option 1").Visible = xlHidden
Worksheets("Option 2").Visible = True
Worksheets("Option 3").Visible = xlHidden
Case "Option 3"
Worksheets("Option 1").Visible = xlHidden
Worksheets("Option 2").Visible = xlHidden
Worksheets("Option 3").Visible = True
End Select
End Sub
Purfleet
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
Thanks Purfleet,
I'd like to use the buttons as well. How would you go about it?
Thanks
Trusted Members
December 20, 2019
Offline
Just create a sub for each button with simular code then assign each sub to a button
Sub Option1()
Worksheets("Option 1").Visible = True
Worksheets("Option 2").Visible = xlHidden
Worksheets("Option 3").Visible = xlHidden
End Sub
Sub Option2()
Worksheets("Option 1").Visible = xlHidden
Worksheets("Option 2").Visible = True
Worksheets("Option 3").Visible = xlHidden
End Sub
Sub Option3()r
Worksheets("Option 1").Visible = xlHidden
Worksheets("Option 2").Visible = xlHidden
Worksheets("Option 3").Visible = True
End Sub
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
Here is the current layout of my file (see attached)
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
I haven't had success. Please see my file.
Thanks for your guidance.
Trusted Members
December 20, 2019
Offline
Morning John
What triggers the sheets to be unhidden? is it the check boxes on the bidder profile?
I have been looking around the form and noted a couple of things
The Private Sub Worksheet_SelectionChange(ByVal Target As Range) was erroring as you had a space after the 3_Thessalon_Bid_Schedule_058 worksheet name
The Subs you created for each worksheet began with a number which VBA doesnt like
I have added buttons to your form (they can be tidied up later) and changed the 3 Subs so that the first one opens Thessalon & La Cloche, the second one opens just parry and the third opens all.
Hopefully this give you some guidance - let me know you get on or if you need anything else
Purfleet
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
Good Morning Purfleet,
This is very helpful.
I greatly appreciate your guidance.
Thank you!
John
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
Hi Purfleet,
I just noticed that after I select an option and I begin filling in the fields on the main Bidder Profile tab that the select tabs hide and I have to go and re-select.
How do I stop that from occurring?
Thanks in advance for your help.
John
October 5, 2010
Offline
Hi John,
The Worksheet_SelectionChange was still executing when you selected cells on the Bidder Profile sheet. This was causing sheets to be hidden as you moved around cells.
I've entered an Exit Sub statement at the top of the sub to prevent the code being run. I didn't delete the Sub in case you decided you needed it. So if you don't need it then you can delete the sub.
Regards
Phil
The following users say thank you to Philip Treacy for this useful post:
Purfleet
1 Guest(s)
