Thirdly, you are doing this: If Evaluate(1) = "=list1" You can see in your Immediate window that no matter what cell you are editing the rngValidation will always point to all the validation cells within the worksheet. If Not rngValidation Is Nothing Then Debug.Print rngValidation.Address Set rngValidation = Target.SpecialCells(xlCellTypeAllValidation) Try this code to clarify that: Private Sub Worksheet_Change(ByVal Target As Range) I believe that you are making the assumption that this is referring to cells within the Target range but it really refers to all the cells with validation within the entire sheet. Secondly, you are doing this: If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub The assumption that you can use the 1 property on any cell is wrong because cells that do not have validation will not have this property available. First of all, using the Worksheet_Change event means that every worksheet change is going to run your code, so Target could be any range not just B14.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |