| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is Workbook sheet change being activated?
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why is Workbook sheet change being activated?
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 "davegb" wrote: 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Why is Workbook sheet change being activated?
On Feb 1, 4:30 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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. "davegb" wrote: 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!- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cells change color when another cell is activated or typed with an | Excel Discussion (Misc queries) | |||
| Macro to run when sheet activated | Excel Programming | |||
| Macro to run when sheet activated | Excel Programming | |||
| Password prompt when sheet is activated | Excel Programming | |||
| Load VBA on Activated Sheet | Excel Programming | |||
Linear Mode
