Changing validation list

[malcmail]

I am beginning to really hate drop down in cell data validation boxes! Apart from the fact they don't trigger a WorksheetChange event I've now a problem I really don't get.

The sheet is protected, the cell with the drop down is not. Based on another sheet the idea is that when the user ends up on this sheet the range of dropdown options changes based on prior answers. The code itself is dead straightforward. I've stripped it back to a simple line. OK two lines for this purpose.

Range("d3").Select
Selection.Validation.Modify Formula1:="=$F$3:$F$4"

Runs nicely on unprotected sheet. Not on protected sheet. Even one where only the user interface is locked. Am I doing something wrong here or is this yet another drawback of these damn things? If so, other than having the code unlock and relock, is there a workaround?

Many thanks

[6StringJazzer]

Selecting a value in a cell with a Data Validation dropdown absolutely does trigger a Worksheet_Change. Ensure that you have Application.EnableEvents = True.

However, I was able to reproduce your other problem. I was surprised but the standard way to work around this is to Unprotect before and Protect after. Also don't bother to Select.

   Unprotect
   Range("D3").Validation.Modify Formula1:="=$F$3:$F$4"
   Protect

[malcmail]

It was clumsy coding to illustrate a point but yes no selection is being made (some days I'm just too lazy!)

I've never had it work re WorksheetChange, but I'll have a look and make sure none of the code is left with the events off. Thanks.

[6StringJazzer]

I set this up as a test based on your description, if it would be of any help.

[malcmail]

ALso didn't know about the way you gave the cell references there. That'll save some time!

[malcmail]

OK I take part of that back - suddenly I use target.column and all is golden. I'll need to go back and see what I did wrong before!

[malcmail]

Hmmmm so it clearly works - I need to revisit some code! Thanks.

Also solved my own problem. I was getting too cute frankly. All I actually needed to do was leave the list and validation as was, but use a formula to change the contents of the list that fall into the validation. Simple really. Sometimes knowing to much is a problem

[malcmail]

Hoping you are subscribed to this. I tried something out this morning. The Worksheet_Change code you gave me clearly worked. HOwever I ran into a problem (which is likely why it didn't work for me at all). Now it may not be an issue but normally I set up my change events to only run when a specific column is changed - the column that has the dropdown validation boxes in it. If I do that the change event won't trigger. Try adding the selection to your code and see if it still works. Either I'm getting something wrong or it just plain doesnt. Either way I'd be interested to know. Thank.