I have this macro in ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
'Activated by a change in any worksheet in the workbook - gets password
from user
' verifies password, and unprotects worksheet
MsgBox "Sheet Change"


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 'EXIT

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

If rFoundShName Is Nothing Then Exit Sub 'EXIT

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
ufPwrdEntry.Hide
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
wsPwrdNames.Visible = False
End 'EXIT
Else
GoTo PwrdForm
End If
End If

wsPwrdNames.Visible = False
End 'EXIT
wsPwrdNames.Visible = False
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub 'EXIT

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
'when active worksheet is deactivated, reset variables and reinstate
passord protection

Application.ScreenUpdating = False

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
bPwrdEntrd = False
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

The MsgBox at the beginning should be popping up every time I make a sheet
change, but it's not showing at all. I've checked in the Immediate Window
to make sure Events are turned on. Any ideas as to what else could cause
this code to be inoperative?
Thanks.

On Tue, 10 Feb 2009 15:59:28 -0700, salgud wrote:

I have this macro in ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
'Activated by a change in any worksheet in the workbook - gets password
from user
' verifies password, and unprotects worksheet
MsgBox "Sheet Change"


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 'EXIT

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

If rFoundShName Is Nothing Then Exit Sub 'EXIT

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
ufPwrdEntry.Hide
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
wsPwrdNames.Visible = False
End 'EXIT
Else
GoTo PwrdForm
End If
End If

wsPwrdNames.Visible = False
End 'EXIT
wsPwrdNames.Visible = False
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub 'EXIT

Private Sub Workbook_SheetDeactivate(ByVal ws As Object)
'when active worksheet is deactivated, reset variables and reinstate
passord protection

Application.ScreenUpdating = False

Set wsPwrdNames = ThisWorkbook.Sheets("sheet1")
Set rPwrdEnt = wsPwrdNames.Range("bPwrd")
bPwrdEntrd = False
Application.EnableEvents = False
rPwrdEnt.Value = bPwrdEntrd
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

The MsgBox at the beginning should be popping up every time I make a sheet
change, but it's not showing at all. I've checked in the Immediate Window
to make sure Events are turned on. Any ideas as to what else could cause
this code to be inoperative?
Thanks.

Weird. I played with this problem for over an hour before I posted here.
Right after I posted, I noticed a "new" icon (just had XL 11 installed) at
the top of the Explorer Window in the VBE. I clicked on the "View Object"
icon which, of course, selected whatever sheet I had currently selected.
And the "Sheet Change" dialog box appeared!

So the question is, why would using the "View Object" icon trigger the
SheetChange event when clicking on the actual sheet tabs didn't? Does this
make any sense at all? Anyone else seen this?