Hide a sheet and Password protect it

adityad

New Member
Joined
Apr 1, 2004
Messages
3
I want to hide a sheet and I can do that. However, what I want to do it that when the user wants to view the sheet I just hid, he has to unhide and then enter a password. Right now tthere is no password.
 
Hi
Code:
If strPassword <> Sheet5.Range("az1") Then MsgBox "Wrong Password": Exit Sub

The password is whatever you want it to be, you just type it in cell az1 on sheet5
 
Last edited:
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi mate.

First put your password in A1. Name the sheet "hidden".

Press Alt+ F11 and look for the "ThisWorkbook" object (rather than a module) and paste in:


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "hidden" Or ws.Name = "ProtectedSheet" Then ws.Visible = xlSheetVeryHidden

Next ws
Application.ScreenUpdating = True

End Sub


Sub ShowSheet()
Dim pword As String
pword = InputBox("Please Enter a Password", "VIP access")
If pword = Worksheets("hidden").Range("A1") Then GoTo UnlockSheet Else: _
MsgBox "Wrong Password entered - access denied", vbOKOnly
Exit Sub
UnlockSheet: _
Application.ScreenUpdating = False
Worksheets("ProtectedSheet").Visible = xlSheetVisible
Worksheets("hidden").Visible = xlSheetVisible
Worksheets("ProtectedSheet").Activate
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
ShowSheet
End Sub

So this wll prompt for a password the minute macros are enabled upon opening the sheet. If the user doesn't enable macros he won't even know the sheet exists. If the wrong password is entered the sheet is usable but the ProtectedSheet remains hidden along with the hidden sheet.

you could alternatively trigger the password request another way - let me know..

Good luck
 
Upvote 0
Obviously the above code assumes at least three sheets, with two named "hidden" and "ProtectedSheet", and the rest named as you will..
 
Upvote 0
Hi,

I have tried the following:
Private Sub Worksheet_Activate()
pword = InputBox("Please Enter a Password", "Unhide Sheets")
If pword <> "Test" Then ActiveSheet.Visible = False
End Sub

However, when I am adding the password I can still see the sheet when I key in the password. Is there anyway to do this without seeing the sheet?

/H
 
Upvote 0
Hi,

I have tried the following:
Private Sub Worksheet_Activate()
pword = InputBox("Please Enter a Password", "Unhide Sheets")
If pword <> "Test" Then ActiveSheet.Visible = False
End Sub

However, when I am adding the password I can still see the sheet when I key in the password. Is there anyway to do this without seeing the sheet?

/H
sure - set up a sheet that you're happy for the user to see, then make all the other sheets VeryHidden. I often hide all the sheets except a welcome sheet. Here's a link to a site about forcing the enabling of macros which is also pretty essential if you're concerned about security!

http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

All the best
 
Upvote 0
cant you just use the vbe and break in again?
 
Upvote 0
cant you just use the vbe and break in again?

You could lock the project VBE and disable the user's personal macro workbook, I suppose. And if all sheets are hidden until macros are enabled and a password entered via an inputbox that should be enough security. If someone's good enough to get through that then they deserve to get in I reckon!
 
Upvote 0
Hi,

Thanks for the input.

However, I have another question:

If I now add this macro is there any possibilty that the end users can't open the file if they don't accept the macro (Disable Macros)? Because if they say Disable they still get access.....

/Henrik
 
Upvote 0
Have all sheets hidden ( xlveryhidden ) when you save it, except for one, which has text "Enable Macros To Use This Workbook", and do the unhiding in the Workbook Open event ( which will only happen if macros are enabled ). Oh, and hide the macro message sheet at the same time. If they are allowed to save it, then you should have macros to rehide stuff back to starting state on Workbook Close.
 
Last edited:
Upvote 0
Have all sheets hidden ( xlveryhidden ) when you save it, except for one, which has text "Enable Macros To Use This Workbook", and do the unhiding in the Workbook Open event ( which will only happen if macros are enabled ). Oh, and hide the macro message sheet at the same time. If they are allowed to save it, then you should have macros to rehide stuff back to starting state on Workbook Close.


Exactly right - a before save event will hide all the sheets but your welcome sheet.....
 
Upvote 0

Forum statistics

Threads
1,224,899
Messages
6,181,627
Members
453,058
Latest member
rmd0725

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