Is there a way that I can password protect a worksheet

  • Thread starter Thread starter Lin4it
  • Start date Start date
L

Lin4it

Does anyone know if there is a way that I can password protect a worksheet
but still leave access to the rest of the workbook?
 
Hi,

Alt +F11 to open VB editor. Double click 'This workbook' and paste the code
below in. This does what you want but isn't secure. If a user doesn't
enable macros they see the sheet. Anyone with even a small amount of
knowledge and Google would view your sheet in seconds. Change "Sheet1" to
whichever you want


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

Mike
 
Missed a line, use this instead

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

End If
End If
Sheets(MySheet).Visible = True
End Sub

Mike
 
To just prevent editing on the sheet go to Tools>Protection>Protect sheet
with a password.

To prevent users from seeing the sheet....................

Format>Sheet>Hide

Tools>Protection>Protect Workbook with a password.

Note: Excel's internal security is very weak so be aware that a determined
user will be able to crack your password quite readily.


Gord Dibben MS Excel MVP
 
Back
Top