password protect a sheet

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Is there a way to prevent someone from viewing a worksheet. I know that I
can hide the worksheet and then password protect the workbook to prevent the
user from unhiding the sheet. I would prefer to still see the tabs but not
allow anyone to view that worksheet.
 
Joe,

Alt+F11 to open VB editor, double click 'This Workbook' and paste the code
below in on the right. Change the name of the worksheet to the one you want
to protect.

You should be aware that there is no foolproof way of doing this and that
this method is a deterrent only.


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ws = ("Sheet2")
If ActiveSheet.Name = (ws) Then
ActiveSheet.Visible = False
response = InputBox("Enter password")
If response = "MyPass" Then
Sheets(ws).Visible = True
Application.EnableEvents = False
Sheets(ws).Activate
Application.EnableEvents = True
Else
MsgBox "Your not allowed to see that sheet"
Sheets(ws).Visible = True
End If
End If
End Sub


Mike
 
Hi,

You can choose Format, Cells, Protection and turn on Hidden. Then you can
select the range you don't want the user to view and change its Number format
to the custom format ;;; and then reprotect the spreadsheet.
 
Not sure why you want to see a sheet tab but not the contents but......

You could format all cells to locked and hidden under
Format>Cells>Protection.

Then Format all cells to white text.

Protect the sheet and not allow users to select locked or unlocked cells or
anything else.

Note: Excel internal security is very weak and passwords easily cracked.


Gord Dibben MS Excel MVP
 
Thanks, I'll try this.

Mike H said:
Joe,

Alt+F11 to open VB editor, double click 'This Workbook' and paste the code
below in on the right. Change the name of the worksheet to the one you want
to protect.

You should be aware that there is no foolproof way of doing this and that
this method is a deterrent only.


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ws = ("Sheet2")
If ActiveSheet.Name = (ws) Then
ActiveSheet.Visible = False
response = InputBox("Enter password")
If response = "MyPass" Then
Sheets(ws).Visible = True
Application.EnableEvents = False
Sheets(ws).Activate
Application.EnableEvents = True
Else
MsgBox "Your not allowed to see that sheet"
Sheets(ws).Visible = True
End If
End If
End Sub


Mike
Click to expand...
 
And how will that allow you to see the sheet tab but not the contents as
your original post required?


Gord
 
Back
Top