Hide and Unhide Worksheets with Check Boxes

kozakb,

Here is some code that uses no controls on the Client Info sheet. Column B has the list of all the sheets whose visibility you want to toggle. Column A is formatted with the font Marlett. When the user clicks in column A next to a sheet in column B, a checkmark will appear and the sheet will become visible. If the user clicks on a checkmark, the checkmark will clear and the sheet will be hidden. Cell A2 toggles Show All/Hide All.

Format A2:A60 with Marlett font. Place the following code in The Client Info sheet module.

HTH,
Maud

Please Login or Register  to view this content.

showsheets1.png

ShowSheets.xlsm

Originally Posted by kev_

I can pull something together for you tomorrow (UK)

These are the options I think you want on "Client Info"
- checkbox to hide/show each working sheet (=58 checkboxes)
- checkbox to show/hide ALL 58 sheets at once
- checkbox to show/hide 30 normally hidden sheets
Is there anything I've omitted?

The 30 hidden sheets are presumably listed in the first 30 rows of your sheet list
- how are they pulled together by GetSheets? do they have something in common? are they simply the first 30 sheets in the workbook?

Thanks for the reply. For the options in your list:

• checkbox to hide/show each working sheet (=58 checkboxes) - Yes
• checkbox to show/hide ALL 58 sheets at once - Yes
• Checkbox to show/hide 30 normally hidden sheets - No, these stay hidden all the time unless a person rights clicks a tab at the bottom and selects unhide from the popup menu.

The 30 hidden sheets are listed in the first 30 rows of the sheet list. That is just the way the macro listed them. I'm at home now but could post the macro tomorrow that I found on the net. I could also post the entire sheet list if that would help. These hidden sheets might also have a certain prefix but I can't remember at the moment.

The check boxes should start on row 10 of the Client Info sheet. We enter our client's details on this page that is referenced in the other sheets. It is a nonprinting sheet.

That being said the sheets in this workbook do not change that often nor do new sheets get added that often so maybe don't worry about the sheet list as I can duplicate the macro for each of the 58 sheets that I want check boxes for.

Appreciate all of the help.

After checking all of the sheet names, there are some parts similar in the names but the trend doesn't go through all of them. I've attached a PDF of the list of sheets from the workbook generated by the GetSheets macro. The numbers on the left side of the list were added after I ran the macro.

Rows 1 to 30 are the normally hidden sheets. The one common theme in the hidden sheet names is that almost all of them have an 'underscore' in them except the last two. I suppose the names of these two sheets could be changed to include an underscore. However, these hidden sheet names are referenced/used in other macros in the workbook so I'm not sure if there is a workbook wide search and replace function that includes macros. As a side note, I did not make this workbook and macros are all new to me.

Rows 31 to 89 are the sheets we enter data on. The sheet called "Sheet List" in row 31 is the sheet I made to run the GetSheet macro.

When generating the list of check boxes maybe the 'underscore' can be referenced to include or not include the sheet name?

Also, instead of making one long list of checkboxes for the data sheets, just wondering if it is possible to have it in multiple columns? Like 10 or so rows per column?

Hi Kev,

Thanks for the reply and the code. Tested your file and it does what I want it to do with the check boxes. I added a worksheet ("Sheet89") but could not get it to add a checkbox. I added the new sheet to your 'ListOfSheets' tab and 'CBoxes' tab but it kept disappearing when I clicked to another tab and back. I'm thinking I was doing it the wrong way. Rereading your post it says those tabs are auto generated which I couldn't get working.

That being said and given your red recommendation that you would not use this method and think the method in post #3 by Maudibe is a better approach, maybe I should go that route. As vba is new to me, I would like to avoid complication.

I did copy the code from post 3 to my Client Info sheet but could not figure how to make it run.

I'm hoping Maudibe is willing to provide some more guidance with running it and answer questions in my original reply to post #3.


Thanks for the help, it is appreciated.

Hi Kozakb,

Here are the answers to your questions

Will the code list all of the sheets or just the 58 we enter data on? I want the normally hidden sheets to stay hidden unless a person right clicks a tab and selects unhide. It will show only the sheets with the names in column B if a checkmark is placed in the adjacent cell in column A. Checkmark is placed by clicking on the cell

Will the list start on row 2 or row 10? I would like row 10 as we enter client info in the first few rows of this Client Info sheet. Here is the adjusted code to start on row 10

Please Login or Register  to view this content.

How would I make the code recognize new sheets if they are added to the workbook? Or is it done automatically? Automatically, just add the sheet names in column B. The sheet is set up to accept a total of 500 sheets, well more than enough. You can remove the cell outline to where ever you want for aesthetic purposes and it will not mess up the code

These would be sheets we enter data on, but could also be hidden sheets. If you want to be able to to show via checkmark then place the sheet name in column B. If you want them to be visible by using only the tabs then do not add to column B. Just make the the names in column B match the actual sheet name although the code is case insensitive

Set up your sheet with the cells A11:A500 as Marlett font. Font for column B is arbitrary. Placing borders around the cells is arbitrary. Place the code in the client Info sheet module. Remove any other coding that shows/hides the sheets. If you have any other code in the Worksheet_SelectionChange event procedure then place this code prior to your code within the procedure. If you have any trouble with your existing codee then post it and I will merge both codes for you.

Please Login or Register  to view this content.

I like using this approach because in your case, the addition and removal sheets requires no changes to coding or number of objects and is zero maintenance.

HTH,
Maud
ShowSheets_Rev1.xlsm

Hi Maudibe,

Thanks for the updated code and answers to my questions. This is going to work like magic! I'll try it out on the weekend or on Monday, as I ran out of time today.

I've quoted a section of your code that I believe refers to the individual sheets. I noticed it gives a 'Range ("A12:A500), which is what you mentioned that I should format with the Marlett font. If I wanted to set up my selections in columns instead of one long list could I set this range to say A12:A30. Then copy the code for the individual sheets and repeat it underneath the code you wrote?

I'm thinking it could be the code between the "Select individual sheet to show" and "End If" (The first one down the list). Not a 100% sure as near the end of the code I quoted it mentions Range ("A1"), so maybe I'll have to include a few more lines than I thought. I'll give it a try and find out. Then my next range could be D12:D30 and so on. I'm not sure how your code references the sheet name in column B, but maybe it has something to do with the 'offset' command as I can't see a column B in the code. While I'm hoping I can figure it out, it might be more complex than my brain is leading me to think.

Originally Posted by Maudibe

Please Login or Register  to view this content.

I appreciate all of the time you've taken to write the code and answer my questions. I'll let you know how it goes next week.

Have a good weekend.
BK

Hi Kozakb,

Kudos on the idea of the multiple column approach. Alteration to the code was minimal. I changed this a bit to use two named ranges. When adding new columns, you can copy range A10:B30 then paste to D10 or G10 or where ever you like on row 10. Selecting multiple cells (aka range) will not trigger the code because of the code line:
If Target.Count > 1 Then Exit Sub

To enable/disable a column is just a matter of updating the two named ranges from within the Excel GUI and not the VB Editor:

SelectAll- This named range has all the cells to the left of the cells with the value SELECT ALL. That would be A11, D11, G11, etc. Any cell included will be activated while any cell excluded would be deactivated (ex SelectAll contains A11, G11 then D11 is deactivated).

SelectSingle- This named range contains the check mark cells for the sheets. The ranges would be A12:A30, D12:D30, G12:G30, etc. Like above, any range excluded will deactivated

To add new sheets, simply add the sheet name (case insensitive). If a sheet does not toggle, check the spelling. You mentioned the second to the last code line being Range("A1").Select. This is a very important statement because it enables the toggling. When you click a check mark cell (ex A13), the code line moves the selection to A1 so A13 becomes available for a second click. If this line was not in the code and you clicked A13, A13 would remain selected. This would mean that to toggle it you would have to select another cell then come back to it (no toggling).

HTH,
Maud

ShowSheets_Rev2.xlsm

NamedRange.png

BTW Kev_ mentions the downside to using checkboxes:
- they are a pain to maintain and set up
- each one triggers a different action
- if you want to sort them (eg after adding another sheet) you need to write a routine to do that
- it's the same for anything that needs to done to a large number of check boxes
- it's easy to end up wasting a lot of time and the code can quickly become cumbersome

While this is true if handling the check boxes individually, it becomes a very easy process using a class instead where the code is written only once for all of the check boxes. It then becomes a matter of just adding the active x controls to the sheet.

Maud

Hi Kev_,

Attached is a calendar control that I built to obtain a start date and an end date. It has 504 labels with 42 labels representing the possible days of each month (x 12). There could have been code for each label's click event but that would have been insane to write let alone mange. Instead, I wrote a class to deal with all label clicks. Here is how it works:

1. All the labels are added to a Privately declared collection (Dim lbCollection As Collection)

Please Login or Register  to view this content.

2. When a user clicks a label the class detects which label was clicked and runs the class MyLabel_Click routine which calls the Get_Dates routing with the label sent as a parameter

Please Login or Register  to view this content.

At this point, any addition code could be added when a label is clicked.

3. The Get_Dates routine then retrieves the label caption (day of the month) and places the date in a textbox as the start date.

4. When a second label is clicked, the same process occurs but the label caption is sent to second textbox as the end date.

5. When a start date and end date have been obtained, clicking the OK button utilizes the start and end dates to perform some function (removed for clarity)

I removed other modules and sheets and left just the code dealing with the calendar. This method could easily be converted to check boxes as well.

Maud

calendar.png

Calendar.xlsm

NP Kev_. Let me know if you ever have a question about it.

Maud

Hi Maudibe,

This has been a long time coming as I work out of the office a lot, but I wanted to say thank you for your help. Your time, assistance and coding was greatly appreciated.

Managed to get this working for our excel table and set up eight columns to show and hide the worksheet tabs.

Table.jpg

Thanks again.
Brian.