Password Protect Unhide Worksheet?

  • Thread starter Thread starter garyh
  • Start date Start date
G

garyh

Is there a way to password protect a worksheet so that the user cannot UNHIDE
the worksheet unless she/he knows the password?

Thanks, in advance, for your help.

G
 
Hi,

There is no real secure way of doing this in Excel but this should deter the
average user.

Alt+F11 to open VB editor. Double ckick 'ThisWorkbook' and paste the code
below in on the right. Change mysheet to the name of the sheet you want to
protect.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
mysheet = "Sheet1"
If ActiveSheet.Name = mysheet Then
Application.EnableEvents = False
ActiveSheet.Visible = False
response = InputBox("Enter password to view sheet")
If response = "MyPass" Then
Sheets(mysheet).Visible = True
Sheets(mysheet).Select
End If
End If
Sheets(mysheet).Visible = True
Application.EnableEvents = True
End Sub

Mike
 
Launch VBE using Alt+F11.
On the left hand side treeview; click the sheet to be hidden. This will open
up the properties for that sheet. Set Visible property to 2- xlVeryHidden.

If you need you can password protect VBE from menu Tools|VBA Project
properties|Protection


If this post helps click Yes
 
slight modification to mikes code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

mysheet = "Sheet1"

If Sh.Name = mysheet Then
Application.EnableEvents = False

Sh.Visible = False

response = InputBox("Enter password to view sheet")

If response = "MyPass" Then

With Sheets(mysheet)
.Visible = True
.Select
End With

Else

Sheets(mysheet).Visible = False

End If
End If

Application.EnableEvents = True

End Sub
 
Thanks for everyone's help. The code works well, but ONLY if the user
selects ENABLE Macros. If the user chooses DISABLE Macros, they can still
unhide the sheet.

Anything that we can set to turn on Macros, by default (no selection
required)?
 
Thanks, Harald. I would like to protect the workbook ... can you provide
guidance (e.g., modify your code)?

Again, thank you for your assistance.

G
 
No code. No macros.

Excel 2007: Review pane, Protect Workbook button, check Structure and
provide password.

Earlier versions: Menu Tools - Protection - Protect workbook, check
Structure and provide password.

Best wishes Harald
 
Noong Martes, Marso 24 2009 00:27:25 UTC+8, si Harald Staff ay sumulat:
There are ways to ensure that the workbook is useless without macros, see
among severar
http://www.cpearson.com/excel/EnableMacros.aspx
but allow me to repeat: just protect the Workbook with a password, and the
user can not unhide sheets.

Best wishes Harald
Click to expand...

It is possible to unhide and hide a worksheet using passwords without securing the opening of a workbook. You have to learn VB codes for Excel. You may use the properties xlSheetVeryHidden and xlSheetVisible
 
Protect the Workbook, not the Worksheet.

HTH. Best wishes Harald
Click to expand...

the below works pretty good for me

Private Sub Worksheet_Activate()
Dim PASSWORD
LINE1:
PASSWORD = Application.InputBox("Please enter the password word", "Password")
If PASSWORD <> "PASSWORD1" Then
MsgBox "Please Supply a valid password", vbCritical
GoTo LINE1
Else
ActiveSheet.Unprotect PASSWORD
End If
End Sub
 
the below works pretty good for me



Private Sub Worksheet_Activate()

Dim PASSWORD

LINE1:

PASSWORD = Application.InputBox("Please enter the password word", "Password")

If PASSWORD <> "PASSWORD1" Then

MsgBox "Please Supply a valid password", vbCritical

GoTo LINE1

Else

ActiveSheet.Unprotect PASSWORD

End If

End Sub
Click to expand...

That does work, but the person can still see the data on the worksheet without having the password. Also, if they don't have the password, there is no way for them to cancel the popup box. You'd be onto a winner if those two bits were added. I'd use it.
 
That does work, MDAddio, but the person can still see the data on the worksheet without having the password. Also, if they don't have the password, there is no way for them to cancel the popup box. You'd be onto a winner if those two bits were added. I'd use it.
 
Back
Top