The Unhide Worksheets utility
About Unhiding Excel Worksheets
Have you been using Excel for some time and occasionally need to hide worksheets? If yes, you will know how easy it is to hide any number of sheets in one operation. However you can only unhide one sheet at a time!
Since there is no practical solution to this problem, I decided to tackle this annoyance by creating a UserForm (interface) and add VBA code to deal with this problem. At the end of this post, you will find a link to download the utility and a PDF document explaining how it works and discusses the code behind it.
On the right you see the form that appears when you launch the command. Upon opening, the list box shows all currently hidden worksheets in the workbook.
The default button is Unhide Sheets. This means that once you have selected one or more hidden sheets from the list you can hit Enter to unhide them. Of course you can click on the button yourself. Click Unhide All to do this that.
It is by design that the list of sheets is not sorted in the form. They are listed in the order that the sheets are positioned in the workbook.
To unhide a worksheet, simply double-click on the name. A screen tip reminds you that to select a range of sheets you need to hold the Shift key or hold Ctrl to select non adjacent sheets. You can also drag your mouse in the list to select an adjacent group of sheets. Click a selected entry while holding Ctrl to deselect it.
Note: Unfortunately it is impossible to select multiple adjacent selections with this listbox. One way you can circumvent this limitation is to select all sheets then hold Ctrl to deselect the sheet(s) you do not want to unhide. A quick way to select all sheets in the list is to select the first entry then press Shift+End on your keyboard.
Making the utility available in every workbook?
You’d think it would be easy to make a utility macro available in every workbook! It’s a bit tricky but it is not a lot of work. Follow the list of steps below to make the Unhide Sheets macro command available in every workbook (old and new) by clicking a button in your Quick Access Toolbar (QAT). The steps are the same for all versions of Excel (2010 and over. Probably earlier too).
The trick to have a VBA procedure available in every workbook is to copy that code in a special workbook named PERSONAL.XLSB. By default the workbook PERSONAL.XLSB does not exist. Additionally, it must be in a very specific folder in your computer. The good news is that you can create this workbook in the correct location in just a few seconds without efforts!
Start by downloading, unzip and open the workbook UnhideSheets.xlsm supplied at the bottom of this blog. If you get the message “Security Warning Macro have been disabled.” at the top of your screen then you’ll have to trust me and click Enable content.
Now let’s create a dummy macro that will create the PERSONAL.XLSB workbook in the correct location, in one go!
- From the View tab find Macros at the far right then click the down arrow allowing you to select Record Macro (as shown across).
- The only thing you need to do here is to select Personal Macro Workbook from the Store macro in list. Click OK.
- Back to the View tab and Macros at the far right. This time click Stop Recording
Done! Excel created the workbook PERSONAL.XLSB in a folder called XLSTART in a precise location in your system. Just to prove it to yourself, while still in the View tab find the Unhide button in the Window group then click on it. Click OK and look at the name of the workbook at the top.
Where is this document stored?
While in the PERSONAL.XLSB workbook hit the F12 key (Save As). In the address bar you see the path to this workbook.You only need to follow the steps to create the PERSONAL.XLSB workbook once per computer. Do not change its location.
Hit Esc to close the Save As dialog box.
Copying the UnhideSheets (code and form) into the PERSONAL workbook
You now have two workbooks open (UnhideSheets.xlsm and PERSONAL.XLSB). Follow these steps to copy the the UnhideSheets procedure and form in the Personal workbook.
- Press Alt+F11 on your keyboard. This is the Visual Basic Environment (VBE).
- If you don’t see a panel on the left of the screen filled with what looks like folders then press Ctrl+R. This is the Project pane. No need to be scared!
- What is important is that you see two VBAProject entries near the top: VBAProject (PERSONAL.XLSB) and VBAProject (UnhideSheets.xlsm).
- If you see a list of all the worksheets, for example Sheet1 (Melbourne) etc, click on the minus sign on the left of the folder to hide them.
- If necessary, expand the Forms and Modules folder in the UnhideSheets.xlsm project.
Do yourself a small favour and remove the entry Module 1 under the PERSONAL.XLSB project. It only contains the dummy macro we recorded earlier. Right-click on it and select Remove Module 1… and click No when prompted if you want to export it.
The Projects panel in your screen should look like the screenshot on the right. We’re almost done, the easiest is yet to come!
Look at the two entries frmShowHiddenSheets and basShowHiddenSheets in the Project panel. The first entry contains the objects for the form. The next one is the code that makes the form work. Problem is that these two objects should be in the PERSONAL workbook. Let’s do just that now.
Using your mouse, drag and drop the entries frmShowHiddenSheets and basShowHiddenSheets over VBAProjects (PERSONAL.XLSB). Expand the folder Microsoft Excel Objects under VBAProject (PERSONAL.XLSB) then expand the Forms and Modules folders. It worked! Our PERSONAL macro workbook has all it needs to have the UnhideSheets objects work.
Now that all objects were copied in the PERSONAL.XLSB project we can save our work.
- In the Project panel, select the PERSONAL.XLSB workbook and click Save. We could have gone in the PERSONAL workbook and saved it from there too. Saving the VBA code also saves the workbook and vice versa.
- Click UnhideSheets.xlsm then Save.
Things you should know about the Personal workbook
Every time you will open Excel from now on this Personal work book will also open. This way all your universal macros become available. However we do not want to see this Personal workbook every time we open Excel. We wouldn’t want to accidentally work in this important workbook. The solution is to hide it, to make it invisible!
Ensure that the PERSONAL.XLSB workbook is the current workbook and in the View tab click the Hide command (above the Unhide we used before). The Personal workbook is now open but invisible. We want it to stay like that.
When you’re done with this project, when you close Excel you will be asked if you want to save the changes to the Personal workbook. What changes you may ask? Clicking Hide changed the visibility of the workbook. This setting needs to be saved!
Adding a button in the QAT to run the ListHiddenSheets.
- Display the UnhideSheets.xlsm workbook (or any other workbook). It is not important from which workbook you customize the QAT.
- Right-click on any button in your Quick Access Toolbar and choose Customize Quick Access Toolbar.
- From the dropdown list Choose command from select Macros. Well look at this! An entry named PERSONAL.XLSB!ListHiddenSheets. This IS cool!
- Obviously select it and click the Add>> button. Use the Up arrow on the right to move the macro entry up in the list. Top is good!
- Not 100% through yet. One last important step. Below the list Customize Quick Access Toolbar click the Modify button.
- Click one icon that you like in that window and in the Display Name box below type Unhide Sheets or anything that makes sense. What you type there becomes the ScreenTip for your new button. Click OK then OK to close everything.
Now let’s Try It
Press Ctrl+N to create a new workbook. Create a few sheets then hide two or three. Finally click on your new Unhide Sheets button and play with it a bit.
Download a Zip file containing the Excel Macro workbook and a PDF document commenting the VBA code for each object in this form.
If you like this post please leave a short message below and tell your friends!