worksheet change not triggering

D

davegb

I've been working on the code below which is in "ThisWorkbook". I
edited the if statements down toward the end, and now the macro
doesn't trigger when I change a value in the sheet. I'm confused,
again!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Dim rFoundShName As Range
Dim rShNames As Range
Dim wsPwrdNames As Worksheet
Dim vResponse As Variant
Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")

Application.EnableEvents = False

wsPwrdNames.Visible = True
bPwrdEntrd = False
PwrdForm:
ufPwrdEntry.Show

Set rFoundShName = rShNames.Find(ActiveSheet.Name, _
LookIn:=xlValues, _
LookAt:=xlWhole)

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

End If
End If
wsPwrdNames.Visible = False

End
Application.Undo

bPwrdEntrd = False
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
bPwrdEntrd = False
End Sub

And suggestions? I'm particularly curious as to how changing the code
down below can prevent the macro from triggering at all!
 
D

davegb

I've been working on the code below which is in "ThisWorkbook". I
edited the if statements down toward the end, and now the macro
doesn't trigger when I change a value in the sheet. I'm confused,
again!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Dim rFoundShName As Range
Dim rShNames As Range
Dim wsPwrdNames As Worksheet
Dim vResponse As Variant
Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rShNames = wsPwrdNames.Range("ShNames")

Application.EnableEvents = False

wsPwrdNames.Visible = True
bPwrdEntrd = False
PwrdForm:
ufPwrdEntry.Show

Set rFoundShName = rShNames.Find(ActiveSheet.Name, _
LookIn:=xlValues, _
LookAt:=xlWhole)

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

End If
End If
wsPwrdNames.Visible = False

End
Application.Undo

bPwrdEntrd = False
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
bPwrdEntrd = False
End Sub

And suggestions? I'm particularly curious as to how changing the code
down below can prevent the macro from triggering at all!

I figured it out! Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top