Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Worksheet Change Event Triggering Multiple Times | Excel Programming | |||
RTD value changes not triggering worksheet change event | Excel Programming | |||
Validation not triggering Change event in Excel XP | Excel Programming | |||
Validation not triggering Change event in Excel XP | Excel Programming | |||
auto-filter change not triggering worksheet_change event | Excel Programming |