Categorized | excel macro and vba

How to hide a sheet in Excel

How to Hide a sheet from users temporarily ?

This option works only if you want Excel to hide the sheet temporarily – subsequent users can always make the worksheet visible again anytime they wish to. To make a worksheet invisible, simply click on the ‘Format’ -> ‘Sheet’ ->’Hide’ option from the menu and it will hide the sheet from view. This is oftentimes the simplest option.

How To Hide a sheet with restricted visibility to other users using VBA ?

While hiding a sheet using the menu option is handy, it leaves the option open any user to unhide the sheet and view the data. To allow access to the sheet only to authorized users, you can use this small excel macro which prompts the user for entering a password when the user tries to open that specific worksheet for the first time. Essentially the macro checks every time the user selects a sheet to see if the user did select the specified worksheet. If he has, the code prompts him to enter the password. If the user enters the correct password, he/she will not be asked for a password again during that session although if he closes the workbook and opens it later, the user will have provide the password again.

You can download an example of how to hide a sheet using VBA here or click on the button below:

This excel macro works by hiding all the columns of the specific worksheet at the time of the opening of the workbook by the user. This is achieved by using the Workbook_Open() function which is triggered automatically anytime a workbook is opened. From then onwards, another function (Workbook_SheetActivate) comes in to play anytime a selects a worksheet.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

Dim correct_pass_given As Integer
Dim hide_sheet As Worksheet

Private Sub Workbook_Open()
correct_pass_given = 0
'Set hide_sheet = Sheet1 ' Give the name of the sheet to hide (This is not the Sheet Tab name but the one that shows outside the bracket.)
Sheet2.Select
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim strPass As String
Dim lCount, number_of_tries_allowed As Long
Set hide_sheet = Sheet1 ' Give the name of the sheet to hide (This is not the Sheet Tab name but the one that shows outside the bracket.)
number_of_tries_allowed = 3 'this is to restrict the number of tries at one go
'MsgBox correct_pass_given

If ActiveSheet.Name <> "The Sheet to Hide" Or correct_pass_given = 1 Then
Else
hide_sheet.Columns.Hidden = True
'Allow 3 attempts at password
For lCount = 1 To number_of_tries_allowed
strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")
If strPass = vbNullString Then 'Cancelled
MsgBox "Password incorrect", vbCritical, "Message"
Else: correct_pass_given = 1 'Correct Password
Exit For
End If
Next lCount
If lCount = number_of_tries_allowed + 1 Then 'They use up their 3 attempts
Exit Sub
Else 'Allow viewing
hide_sheet.Columns.Hidden = False
End If
End If
End Sub

Now since the password is going to be stored within the VBA code itself, we will need to restrict the users from being able to access the VBA code. To achieve this, simply right click on the VBA project tree in the VBA editor’s project window and choose ‘VBAProject Properties’. In the second tab, enter a password and turn on the ‘Lock project for viewing’ check box. This will protect your VBA code from being visible to users and this protecting the password from being disclosed.

How to permanently hiding a worksheet with complete invisibility

Now there may be cases when you would like to simply hide a worksheet and forget about it. In cases where you do not want to allow anyone else to view the worksheet, you can make a worksheet very hidden. While in the VBA editor, press F4. In the Properties Window, in the “Visible” field choose xlSheetVeryHidden.

Alternatively you can use the following VBA code to make a sheet very hidden during runtime.

1	hide_sheet.Visible = xlSheetVeryHidden

What this essentially does is to hide a worksheet in a manner that it does not show up both when viewing the workbook and in trying to unhide the the sheet by using the method specified in point 1 above. The only way the hidden worksheet can now be made visible is by executing the opposite of the above command, which is:

1	hide_sheet.Visible = xlSheetVisible

You can restrict the user from being able to view the VBA code by password protecting the workbook in the same manner as described in point 2. (Right click on the project window in the VBA editor and assign password.) Once you carry out both these steps, your worksheet will be hidden from everyone forever.

(A word of caution -None of the techniques described above are 100% “tamper-proof”.)