Data Validation within VBA Loop

[Ramses505]

I have a sheet with a load of cells filled with dropdowns (same validation drop down in every cell) - the drop downs contain a list of names from a 'named range' and the cells are filled with a validation rule.

When the list of names is edited (the list is on another sheet) - I want to loop through the existing cells that were filled in and blank any cells with names that are no longer on the named range list (the data validation list), so that people know they need to be filled with nam,es currently on the list.

I can do the looping bit (I think) but I don't know how to compare the contents of the cell to the validation list.

Any help would be much appreciated.

Thanks

 

[Ramses505]

Rick, I have copied your latest code into my Workbook and it does seem to do what I required, not fully tested yet but does work on my test sheet. Thank You.

I have hijacked a line of that code to use elsewhere and I am having a problem with that. As it does relate to your posted code I hope it is ok to deal with that in this thread rather than starting another.

I have a bank of cells that are filled in with the validating drop down discussed in my original question – once the drop downs have set a name from the “ActiveStaff” named range (not “StaffList” as I originally told you, bad memory !), I need to colour the cells background to make them easy to understand – jake blue, sam red, emma yellow etc. I have some Lookup code that works and I was using the Worksheet_Change(ByVal Target As Range) event to do the work. Obviously if someone edits the ActiveStaff list then some of the existing names fail the Lookup (no longer on the list), so I thought I would use a line of the code you gave me like this;

If Range("ActiveStaff").Find(Target.Value, , xlValues, xlWhole) Is Nothing And Target.Value <> "" Then

To test them prior to calling the Lookup.

You can see I edited the code slightly to change the reference of the find value to Target.Value. The code fails with the message;

“Method ‘Range’ of object ‘_Worksheet’ failed”

I had tested the code first by using it on a button like this;

  If Range("ActiveStaff").Find(ActiveCell.Value, , xlValues, xlWhole) Is Nothing And ActiveCell.Value <> "" Then
    MsgBox ActiveCell.Value & " Not Found"
  Else
    If ActiveCell.Value = "" Then
      MsgBox " No Value to Test !"
    Else
       MsgBox ActiveCell.Value & " Found"
    End If
  End If

This seems to work returning a message of “ Name Found ” for all the names on the ActiveStaff list, “No Value to Test” for Blank Squares and “ Not Found “ for names not on the list. I had hoped I had found a really useful technique that I would be able to use in several other places.

I am hoping that I have messed up somewhere and that you might be able to see where.

Any further help would be most welcome.

Thanks again for your help, Regards Ramses

 

[Rick Rothstein]

I have a bank of cells that are filled in with the validating drop down discussed in my original question – once the drop downs have set a name from the “ActiveStaff” named range (not “StaffList” as I originally told you, bad memory !), I need to colour the cells background to make them easy to understand – jake blue, sam red, emma yellow etc. I have some Lookup code that works and I was using the Worksheet_Change(ByVal Target As Range) event to do the work. Obviously if someone edits the ActiveStaff list then some of the existing names fail the Lookup (no longer on the list), so I thought I would use a line of the code you gave me like this;

If Range("ActiveStaff").Find(Target.Value, , xlValues, xlWhole) Is Nothing And Target.Value <> "" Then

To test them prior to calling the Lookup.

You can see I edited the code slightly to change the reference of the find value to Target.Value. The code fails with the message;

“Method ‘Range’ of object ‘_Worksheet’ failed”

It is hard to know what the problem without seeing all the code leading up to the one you posted including the procedure header row. Can you post that for us (me)?

 

[Ramses505]

Rick, thanks for reading all that, of course, your right, what can you tell from a single line.

I am trying to get a sheet working and I am also trying to learn something (if I can) - I have used the code I quoted to create a function that returns whether or not a passed string value is in a passed range and that has (somehow) cured the problem - problem solved, maybe... but I don't understand why.

I will put together the code and post that tomorrow so that if you have the time you might tell me what you think the issue is because then I will have learned something.

Thanks again.