Sometimes we need to hide or unhide sheets in Excel. If you develop an advance application with lots of excel databases and VBA forms, then you may need to hide the database from the users. So then they can only alter data with userforms. In this post first I will explain you how to hide or unhide worksheets manually. Then I will teach you how to do it using VBA.
Assume we have an Excel workbook like this. So we have 3 sheets in our file.
If we want to hide one of the sheets manually we can do it as follows. First right click on the worksheet name you want to hide. Then click on “Hide”.
As I click on the “Sheet 1”, it will hide the “Sheet 1”
And if you need, you can hide several sheets at once. What you need to do is select all the sheet names you want to hide while holding down Ctrl key in your keyboard. (However you can’t select all the worksheets in the workbook to hide. Because there should be atleast one visible sheet in the workbook.) Then right click on one of the worksheet name you want hide and click on “Hide”.
This will hide all selected sheets.
Next let’s learn how to Unhide sheets manually. You can do it as follows. First right click on one of visible worksheets. Then click on Unhide.
Then it will show list of hidden sheets like this.
Select the worksheet you want to unhide and then click OK. Selected worksheet will become visible.
Now let's look at how to hide a worksheet using VBA. Assume you have worksheet call Sheet1. And you should have at least one more sheet in the workbook in addition to Sheet1. You can hide Sheet1 like this.
|
Sub HideSheet() Worksheets("Sheet1").Visible = False End Sub |
And if you want to unhide that sheet using VBA then you can do it as follows.
|
Sub UnhideSheet() Worksheets("Sheet1").Visible = True End Sub |
However if you use above method to hide sheets, then users have ability to unhide them manually if they want. But there is another method you can hide sheets which doesn't allow manual unhide. If you use that method then worksheets can be unhidden only using a above unhide code. Here is the code.
|
Sub VeryHidden() Worksheets("Sheet1").Visible = xlVeryHidden End Sub |
So assume we have 3 sheets like this.
And we run above code.
As you can see the Sheet1 is now in hidden state. But you can’t unhide it manually as it doesn’t show hidden sheet.
So you need to use above Unhide code to make it visible.