How to Hide a sheet from view

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
Is it possible to hde one of the sheets of a workbook. These are reference sheets and the user should not use them
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi, you can hide a sheet by selecting Format|Sheet|hide. Unfortunately this wont stop nosey users as sheets can be unhidden by using the same method. To hide the sheet and prevent showing it via the menu you need to run a macro.

Heres an example...
Code:
Sub HideSheet()
'Replace Sheet1 text with the name of your sheet
Sheets("Sheet1").Visible = xlVeryHidden
End Sub
 
Upvote 0
Hit ALT+F11

Paste the below code to ThisWorkbook
*assuming that you want to hide sheet2*

Private Sub Workbook_Open()
Worksheets("Sheet2").Visible = False
End Sub

Sub reveal()
Dim passwd As String
Dim passwd1 As String
passwd1 = "password"

If Worksheets("sheet2").Visible = False Then
If InputBox("Authority to view hidden data...", "Password required!") = passwd1 Then
Worksheets("sheet2").Visible = True
Else
Worksheets("sheet2").Visible = False
MsgBox ("You are not authorized." & vbOKOnly)
End If
Else
Worksheets("sheet2").Visible = False
End If
End Sub


-------------------
If you want to hide other sheet or you've renamed the sheets
then replace all sheet2 with "name" where name is the name of your sheet.

hope the above helps
 
Upvote 0
to reveal the hidden sheet, just to go
Tools -> Macro -> Macros -> ThisWorkBook.Reveal

the password is located

passwd1 = "password"

change it.
 
Upvote 0
Hi anis & dr_shivan. Note that Sheet1.Visible = False is the same as you manually hiding the sheet which means it can be revealed again by simply selecting Format|Sheet|Unhide. See my post above regarding how to make it "very" hidden.

After a sheet is hidden using the xlveryhidden constant then the workbook structure can be protected. However, you need to bear in mind Excel does not contain bullet proof security.
 
Upvote 0
Oooo i didn't know that, hmmm so with the xlveryhidden, the sheet won't be shown in the format|sheet|unhide?

if so, then can i just modify my macro to the xlveryhidden.

Thanx for pointing that out Parry
 
Upvote 0
dr_shivan said:
with the xlveryhidden, the sheet won't be shown in the format|sheet|unhide?

That's right. You have to unhide it through the sheet properties in the VBE, or with VBA code to do it while a macro is running.
 
Upvote 0
yep..read ur post...it appears just after i posted my msgs that's y i didnt notice it b4...thanx for that new knowledge nway :)
 
Upvote 0
The suggestion from Parry to use x1VeryHidden is the most secure.

Now, a reasonably smart user can go to Macros and look around for the routine and locate the password.

Any way to HIDE code like THAT?
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top