Shortcut to show and hide sheet tabs

Hiding the Sheet Tabs is something that can be very effective and useful if you are sharing your Excel workbook with colleagues. You may not want them going in and meddling with your calculation sheets, you may have added navigation buttons for the users instead or you may just want the workbook to look more tidy. Whatever the reason, for something so common and useful it is a surprise there is no keyboard shortcut in Excel to toggle your sheet tabs on and off.

 

Currently you have to go to File, Options, Advanced and then scroll down to the below option to toggle between showing and hiding sheet tabs.

 

togglesheettabs1

 

There is a way you can create a shortcut using a simple VBA module though. Firstly, you need to add a new macro to your Personal Macro Workbook. If you haven’t done that before, follow these steps:

 

 

togglesheettabs1

 

In this example to populate some VB code I have just input ‘testing’ to cell D4 and stopped the macro recording. If you click on Alt + F11 to open the VBA window you will see there is now a folder called ‘VBA Project¬†(PERSONAL.XLSB)’ on the left hand panel.¬†This will produce the following module:

 

togglesheettabs2

 

Whenever you hit the shortcut Ctrl + K this will now run.

 

So now we need it to toggle whether the sheet tabs are visible or not, rather than just randomly update cell D4 with “testing”. To do this, update your VB code to the below:

 

togglesheettabs3

 

As well as the Ctrl + K shortcut, if this is something you are going to regularly use it may be worth adding a shortcut to your Quick Access Toolbar.

Leave a Reply

Your email address will not be published. Required fields are marked *