Hide a sheet and Password protect it

[adityad]

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.

 

[Taul]

Hi

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

 

[wilcowoods]

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

 

[wilcowoods]

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

 

[hpdaxelsson]

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

 

[wilcowoods]

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

 

[diddi]

cant you just use the vbe and break in again?

 

[wilcowoods]

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!

 

[hpdaxelsson]

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

 

[GlennUK]

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.

 

[wilcowoods]

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.....