Why is Workbook sheet change being activated?

[davegb]

The code below protects certain worksheets in a workbook from being
edited by unauthorized users. What I want it to do is show the
userform (ufPwrdEntry) when someone tries to edit any of the named
sheets. Anyone can look at the sheets, but only the owner with the
password can edit the sheet. "Sheet1" in the workbook contains a list
of sheetnames and passwords. Once the user enters the password and the
macro checks to see if it's the correct password for that sheet, the
user then has full access to that sheet.

The deactivate macro at the bottom is supposed to test the sheet name
and if it's on the list, reset the bPwrdEnt boolean variable to false
so the sheet can no longer be edited unless the password is entered.

' PUBLIC VARIABLES FOR THIS MODULE ARE DECLARED IN
MODPASSWORDCHANGE
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Dim vResponse As Variant

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
If rPwrdEnt.Value = "True" Then
Exit Sub
End If
Set rFoundShName = rShNames.Find(ActiveSheet.Name, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If rFoundShName Is Nothing Then
Exit Sub

End If

wsPwrdNames.Visible = True

PwrdForm:
ufPwrdEntry.Show

If sPwrd = rFoundShName.Offset(0, 1).Value Then
bPwrdEntrd = True
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
Else
vResponse = MsgBox("Incorrect Password! Click OK to try again,
Cancel to exit", _
vbOKCancel)
If vResponse = vbCancel Then
Application.EnableEvents = True
ufPwrdEntry.Hide
Application.Undo
End
Else
GoTo PwrdForm

End If
End If
wsPwrdNames.Visible = False

End
Application.EnableEvents = False
Application.Undo

'bPwrdEntrd = False
'rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
' if user named sheet is deactivated, set bPwrdEnt to false and save

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")
Set rFoundShName = rShNames.Find(ActiveSheet.Name, _
LookIn:=xlValues, _
LookAt:=xlWhole)

If rFoundShName Is Nothing Then
Exit Sub
'On Error GoTo 0
End If
Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
bPwrdEntrd = False
rPwrdEnt.Value = bPwrdEntrd
End Sub


For some reason, when I enter any of the named sheets, the userform is
displayed right away. I want it to only be displayed if someone tries
to edit the sheet. Can anyone see what is causing the macro to
activate anytime the named sheets are activated?

Thanks!

 

[Jim Thomlinson]

I have not looked too closely but it appears as if perhaps your sheet
deactivate code causes a change on your password sheet

rPwrdEnt.Value = bPwrdEntrd

this will fire your sheet change code (like i said I have not looked too
close but it might be worth looking at... Add a break point in your
deactivate procedure and see what is firing and when by stepping throught the
code line by line... You may need to add Application.EnableEvents = false /
true to get around this problem...

 

[davegb]

I have not looked too closely but it appears as if perhaps your sheet
deactivate code causes a change on your password sheet

rPwrdEnt.Value = bPwrdEntrd

this will fire your sheet change code (like i said I have not looked too
close but it might be worth looking at... Add a break point in your
deactivate procedure and see what is firing and when by stepping throught the
code line by line... You may need to add Application.EnableEvents = false /
true to get around this problem...
--
HTH...

Jim Thomlinson
Thanks, Jim! That did it.



















- Show quoted text -