In the attached Sample Workbook, you will find 4 tabs. A "Summary Sheet" tab and 3 Data Tabs. On the Summary Sheet in Column 'A', there are check boxes next to each Bid Package. Each Bid Package will have it's own tab in the original document, but in the sample workbook attached, there are only 3 Bid Package Tabs provided. At the top of Column 'P', you will find a toggle button labeled "Show All / Hide Inactive Bid Packages". I want this toggle button to be able to hide all rows on the summary sheet and the corresponding Bid Package Data Tab for any rows where the check box is blank or inactive. And then, once the toggle button is pressed again, it should unhide all rows and tabs making everything visible. The desire here is for me to be able to select which Bid Packages are Active, meaning which data tabs need to be visible in the workbook and which rows need to be visible on the Summary Sheet, by simply checking the corresponding box in Column A of the Summary Sheet, and then pressing the toggle button at the top of Column P. I want to eliminate the need for manually hiding rows on the summary sheet and manually hiding tabs in the worksheet. What is the proper macro code that should be used for the toggle button to properly accomplish this? Please keep in mind that I am a novice here, and will need good step by step instructions. Thank you!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to Use a Toggle Button to Hide Rows and Tabs based on a Check box Form Control
Answers
In order to show and hide rows you need a column by which to differentiate them, like Active = 1, Inactive = 0 or Active = True and Inactive = False. Once you have such a column you don't need code. Just apply a filter. Once you have a fitler working you can come back to your button idea and record a macro as you set the filter and attach the macro to the button.
The sheet management I would approach differently. Consider this:-
- All Bid sheets are hidden by default.
- Column B has a hyperlink that takes you to the bid sheet that cell specifies.
When you follow that link the sheet is made visible.- A normal hyperlink can't do that. You would need to use the SelectionChange event.
- Use the Worksheet_Deactivate event to hide the sheet again as you leave it.
There is a possible disadvantage in this system because you wouldn't be able to switch from bid to bid. You would need to go back to the Summary first. However, this drawback can be made into an advantage. You might add a button or otehr mechanism to each sheet which prevents it from being hidden and thereby enables switching. The advantage would be that you would only have the two bid tabs that you are currently working with.
Discussion
1. Ensure all summary sheet rows and bid tabs are visible by pressing the toggle button to unhide all.
2. Check the box in Column A to select which Bid Packages (rows & tabs) need to be Active for this particular job. Again this could be any combination of the 174 Bid Packages listed.
3. Then press the toggle button again to hide all "Inactive" or unchecked Bid Packages (rows and tabs).
I don't want to just see two tabs, I need all active rows and associated bid tabs visible. Does using the Checkbox in Column A appropriately serve as the differentiation column? Active = Check, Inactive = No Check. What is the step by step process to get the toggle botton to hide all inactive rows and bid tabs when pressed, and unhide them all when pressed again
2. You need a cell where you specify the "Active Job". Ideally, there would be a validation dropdown from which you select the job to activate.
3. For the purpose of this dropdown you need a list of jobs. That could be a single-column named range on a hidden sheet or it could be a worksheet where you list a lot of details about each job.
Whichever way you use to fill the cell, it will show the "Active Job". Hiding tabs and rows is a simple thing once you have the setup as described. Look at this thread. It's so near to what you will need that you can copy/paste. Just change the column to point at your column of job numbers.