The button actually triggers a macro, details of which are below.
- Open a new excel workbook
- Start to record a new macro
- Give the macro a name “unhide_all_sheets”, and store the macro in “Personal
Macro Workbook”
- Type a short string into any cell, then stop the macro recording
- Now, lets edit the macro.
Via the keyboard, press “ALT + F11”. - In the “Microsoft Visual Basic for Applications” window,
in the “Project” pane, find and open the newly created module in the “PERSONAL.XLSB” VBAProject.
- Replace the code with the code below Sub unhide_all_sheets()
- Close the window, and confirm that the macro is available by clicking on the
menu options
View > Macros
- To add this macro as a button in the excel ribbon, click on the “Customize
Quick Access Toolbar” button at the top of excel, and then click on the “More
Commands” option
- Add the macro to the toolbar
- In the “Choose commands from” change the dropdown to show “Macros”
- Click on the “PERSONAL.XLSB!unhide_all_sheets” macro
- Click on “Add” to add this macro onto the right-side window
- Optionally click on the “Modify” button to change the icon for the macro,
and then click OK to save changes
- Test the button by creating and hiding new excel sheets in the same workbook to show that the hidden sheets are unhidden.
For Each sh In Worksheets: sh.Visible = True: Next sh
End Sub
