• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How do I add a feature to unhide tabs|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / How do I add a feature to unhide tabs|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHow do I add a feature to unhide ta…
sp_PrintTopic sp_TopicIcon
How do I add a feature to unhide tabs
Avatar
John Fazio
Member
Members

Dashboards

Power Pivot

Power Query
Level 0
Forum Posts: 27
Member Since:
June 7, 2019
sp_UserOfflineSmall Offline
1
March 20, 2020 - 8:33 am
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
March 20, 2020 - 3:02 pm
sp_Permalink sp_Print

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

Avatar
John Fazio
Member
Members

Dashboards

Power Pivot

Power Query
Level 0
Forum Posts: 27
Member Since:
June 7, 2019
sp_UserOfflineSmall Offline
3
March 21, 2020 - 12:18 am
sp_Permalink sp_Print

Thanks Purfleet,

I'd like to use the buttons as well.  How would you go about it?

Thanks

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
March 21, 2020 - 12:28 am
sp_Permalink sp_Print

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

Avatar
John Fazio
Member
Members

Dashboards

Power Pivot

Power Query
Level 0
Forum Posts: 27
Member Since:
June 7, 2019
sp_UserOfflineSmall Offline
5
March 21, 2020 - 2:48 am
sp_Permalink sp_Print

Here is the current layout of my file (see attached)

sp_PlupAttachments Attachments
  • sp_PlupImage Excel-file-layout.PNG (240 KB)
Avatar
John Fazio
Member
Members

Dashboards

Power Pivot

Power Query
Level 0
Forum Posts: 27
Member Since:
June 7, 2019
sp_UserOfflineSmall Offline
6
March 21, 2020 - 8:47 am
sp_Permalink sp_Print

I haven't had success.  Please see my file.

Thanks for your guidance.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
7
March 21, 2020 - 3:38 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
John Fazio
Member
Members

Dashboards

Power Pivot

Power Query
Level 0
Forum Posts: 27
Member Since:
June 7, 2019
sp_UserOfflineSmall Offline
8
March 21, 2020 - 9:42 pm
sp_Permalink sp_Print

Good Morning Purfleet,

This is very helpful.

I greatly appreciate your guidance.

 

Thank you!

John

Avatar
John Fazio
Member
Members

Dashboards

Power Pivot

Power Query
Level 0
Forum Posts: 27
Member Since:
June 7, 2019
sp_UserOfflineSmall Offline
9
March 21, 2020 - 10:38 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
10
March 27, 2020 - 10:56 am
sp_Permalink sp_Print sp_EditHistory

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
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Aron Samuels
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27239

 

Member Stats:
Guest Posters: 49
Members: 31891
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.