Protecting a Sheet - Issue

[seanryanie]

I have the following code that I run to protect a sheet

ActiveSheet.Protect Password:="xyz", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Sheets("Visit Log").Select
ActiveSheet.Protect Password:="xyz", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Range("D7").Select

I have all required cells within the sheet "Visit Log" unlocked. These
unlocked cells are Data Validation drop down lists which run a change
event code when selected (just changes the background colour)

My problem is that when my sheet is protected with the above code, the
Change event code doesn't work

Any ideas?

Thanks

 

[Guest]

I just double-checked (Excel 2003) and had no trouble detecting a
Worksheet_Change event based on selecting from an Unlocked cell with a list
via data validation in it. So have to look elsewhere.

I recently ran into similar situation and for a while I thought the _Change
wasn't firing in a group of merged cells set up with data validation/list.
What actually turned up in that situation was that there was another user
defined function that used Application.Volatile in it that was interrupting
the processing in the _Change code and never returning control to the
_Change() code once it had done so.

I think you're going to have to single step through the code to see all that
is going on and determine if something like that is happening. Just put a
breakpoint early on in your _Change() event code (or just a Stop instruction)
and then when you get there, start using [F8] to single step through it all,
following closely to see where it goes astray or fails completely.

 

[Guest]

Another thought: since you are using the change event to make a change, you
may be getting in an infinite loop within the _Change() event itself. Be
sure you turn off EnableEvents before executing any code in there to change
anything.

Consider this:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Event Fired"
Range("G8") = "changed from code"
End Sub

In this case, G8 is actually a locked cell, but no matter. If you run it
like this, you'll get the "Change Event Fired" message over and over until
you use [Ctrl]+[Break] to stop it.

Change it a little like this:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Change Event Fired"
Application.EnableEvents = False
Range("G8") = "changed from code"
Application.EnableEvents = True
End Sub

And it will work and make the change, but since G8 is locked and the sheet
is protected, the error message comes up telling me that I'm trying to alter
contents of a locked cell on a protected sheet - as expected.