How to Hide Sheet in Excel with or without VBA?

Video: How to Hide Worksheet in Excel?

Hide Sheet in Excel

When I was creating an excel dashboard, there were multiple sheets which I used for calculation purpose and never wanted anybody to make any changes on it. So I found very awesome feature to hide an excel tab:

Though there are multiple ways to hide the excel sheet tab:

Excel VBA Master Tool Kit

Simple Way to Hide Sheet in Microsoft Excel

  • Select the Sheet which you want to hide and Go To “Cells” Section under Home Menu as shown in below picture:
How to Hide Excel Sheet and Unhide

Once you hide the sheet, you may unhide the same through “Unhide Sheet” option.

Note: Sheet should not be protected while hiding the sheet

Hide Sheet or Tab in Excel Using Shortcut

  • You may also hide the sheet in just a single right click on Sheet Name and unhide the same on just one right click on any sheet tab name. See below:
Unable to find hidden sheets in Excel

Disable the option to Unhide Sheet in Excel - VBA

  • You learnt how to hide the sheets above however there are instances when you do not want to allow any user to see Hidden Sheets. So you can do this also by using “Very Hidden” feature
  • Go To VBA Editor Window either of below step:
  • Press “ALT+F11” on the workbook where you wanted to perform this “Very Hidden” functionality
  • Or Right Click on any Sheet Tab, Click on View Code
  • Here you can see in below image that how to open VBA Editor Window
Open Excel VBA Editor Window

Hide a Worksheet So That It Can Not be Unhidden

  • Once you open the VBA Editor window, you will see your sheet name on the left side as below:
Very unhide Excel Sheet
  • Click on “View” from “Menu” option. Then Go To “Properties Window”. See Below:
Dont Show hidden sheets in Excel
Unhide a Sheet that has been ‘Very Hidden’
  • Go To “Properties” window and select “2–xlSheetVeryHidden” under “Visible” category. Please see below:
Dont Show hidden sheets in Excel
  • Now Go back to excel workbook and when you will right click on sheet tab, you will not find the option to Unhide the sheet. See below Image:
Open Excel VBA Editor Window

Hide/Unhide Worksheets Using VBA

Lets learn how you can hide sheets in Excel through Excel VBA Code:

Hide All Worksheets Using VBA

 

Below is the VBA Code:

'Hide All Sheets in Excel using VBA Code
Sub HideALLSHEETS()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws
End Sub

So this is a complete guide of hiding sheets/tabs in a workbook. Hope you liked it.

Recommended Articles

Similar Posts

VBA Code to Re-link MS Access Link Tables

VBA Code to Re-link MS Access Link Tables

Byexcelsirji

In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location

VBA Code to Check if File Exist in Folder

VBA Code to Check if File Exist in Folder

Byexcelsirji

To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.

Leave a Reply

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

Excel VBA Master Tool Kit