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?

On Tue, 10 Feb 2009 18:41:52 -0500, JLGWhiz wrote:

The SheetChange event is for changes to the sheet, not changing selection of
sheets. Clicking the name tab does not change the sheet. You would need to
change a cell content on the sheet to trigger the event. If you want to
trigger the macro by changing the active sheet, then use the SheetActvate or
SheetDeactivate event.



"salgud" wrote in message
...
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?

Thank you, thank you, thank you. I had completely forgotten that when I
first wrote this code, part of it was supposed to run when the user tried
to make a change to the data in the sheet, and the other part (not shown in
my post) runs when the sheet is deactivated. So the code is correct, but my
interpretation of what it was doing was wrong. Duh!