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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 

dr_shivan

Active Member
Joined
Jan 3, 2005
Messages
298
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
 

dr_shivan

Active Member
Joined
Jan 3, 2005
Messages
298

ADVERTISEMENT

to reveal the hidden sheet, just to go
Tools -> Macro -> Macros -> ThisWorkBook.Reveal

the password is located

passwd1 = "password"

change it.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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.
 

dr_shivan

Active Member
Joined
Jan 3, 2005
Messages
298

ADVERTISEMENT

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
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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.
 

anis

Board Regular
Joined
Dec 28, 2004
Messages
87
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 :)
 

UHsoccer

Well-known Member
Joined
Apr 3, 2002
Messages
1,023
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?
 

Forum statistics

Threads
1,147,666
Messages
5,742,510
Members
423,734
Latest member
123hmMission

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
Top