Denying access to a worksheet

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Can anyone tell me how I can deny access to a particular worksheet within a
workbook. I want it so that when someone clicks on the tab to access a
particular worksheet, unless they type in a password, they are denied
access. Thanks in advance

Mark
 
One way:

Put this in the ThisWorkbook code module (right-click on the workbook's
titlebar and choose View Code):

Assume that Sheet3 is to be protected, and if the correct password is
not entered, then sheet1, cell a1 is to be activated:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Const csPWORD As String = "drowssap"
Dim vResult As Variant
If Sh.Name = "Sheet3" Then
Sh.Columns.Hidden = True
Do
vResult = Application.InputBox( _
Prompt:="Input Password", _
Title:="Sheet3 Access", _
Type:=2)
If vResult = False Then Exit Do
Loop Until Len(vResult) > 0
If CStr(vResult) <> csPWORD Then
With Application
.EnableEvents = False
.Goto Worksheets("Sheet1").Range("A1")
.EnableEvents = True
End With
End If
Sh.Columns.Hidden = False
End If
End Sub

Note that it's pretty easy to bypass this type of protection (disabling
macros on startup is one way - if that's a possibility, the sheet should
be hidden on close, and made visible on open. Use these event
macros:

Private Sub Workbook_Open()
Sheets("Sheet3").Visible = xlSheetVisible
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet3").Visible = xlSheetVeryHidden
End Sub

But these will also keep only the uncurious out, since there are
numerous ways to defeat worksheet and macro protection.
 
Thanks...very useful

best regards


JE McGimpsey said:
One way:

Put this in the ThisWorkbook code module (right-click on the workbook's
titlebar and choose View Code):

Assume that Sheet3 is to be protected, and if the correct password is
not entered, then sheet1, cell a1 is to be activated:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Const csPWORD As String = "drowssap"
Dim vResult As Variant
If Sh.Name = "Sheet3" Then
Sh.Columns.Hidden = True
Do
vResult = Application.InputBox( _
Prompt:="Input Password", _
Title:="Sheet3 Access", _
Type:=2)
If vResult = False Then Exit Do
Loop Until Len(vResult) > 0
If CStr(vResult) <> csPWORD Then
With Application
.EnableEvents = False
.Goto Worksheets("Sheet1").Range("A1")
.EnableEvents = True
End With
End If
Sh.Columns.Hidden = False
End If
End Sub

Note that it's pretty easy to bypass this type of protection (disabling
macros on startup is one way - if that's a possibility, the sheet should
be hidden on close, and made visible on open. Use these event
macros:

Private Sub Workbook_Open()
Sheets("Sheet3").Visible = xlSheetVisible
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet3").Visible = xlSheetVeryHidden
End Sub

But these will also keep only the uncurious out, since there are
numerous ways to defeat worksheet and macro protection.
Click to expand...
 
Back
Top