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

 

[JoeMo]

Would you want an event macro that automatically blanks cells with names that are no longer on the validation list whenever a change is made to the validation list? If yes, give us some details like: (1) the name of the named range that holds the validation list; (2) the name of the sheet the cells to be checked for outdated names are on and the range those cells occupy.

 

[Domenic]

Actually, Joe's suggestion might be a better way of doing it. But since I've already written something, I'll share it. The following macro assumes the following...

1) The sheet containing the data is the active sheet.

2) Column B, starting at Row 2, contains the data.

3) The named range is called MyNamedRange.

Change the references to the column and named range, accordingly.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] ClearOldItemsFromCells()

    [COLOR=darkblue]Dim[/COLOR] DVRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] DVList [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] Cel [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] MatchVal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Cnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    [COLOR=darkblue]If[/COLOR] LastRow < 2 [COLOR=darkblue]Then[/COLOR]
        MsgBox "No data found...", vbExclamation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] DVList = ActiveWorkbook.Names("MyNamedRange").RefersToRange
    
    [COLOR=darkblue]Set[/COLOR] DVRange = ActiveSheet.Range("B2:B" & LastRow)
    
    Cnt = 0
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Cel [COLOR=darkblue]In[/COLOR] DVRange
        [COLOR=darkblue]If[/COLOR] Cel.Value <> "" [COLOR=darkblue]Then[/COLOR]
            MatchVal = Application.Match(Cel, DVList, 0)
            [COLOR=darkblue]If[/COLOR] IsError(MatchVal) [COLOR=darkblue]Then[/COLOR]
                Cel.ClearContents
                Cnt = Cnt + 1
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] Cel
    
    MsgBox "Number of cells cleared:  " & Cnt, vbInformation
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Ramses505]

JoeMo - thanks for the reply. Generic code will do (if I know where to put it !)

My list is called "StaffList" and there are a few ranges that I will want to check - my sheets are just sheet1 to sheet6 etc.

Thanks for the help, I am interested in learning something at the same time if I can.

 

[Ramses505]

Domenic, Thanks for taking the time top reply. I think I get how this works and I can probably adapt it so I can pass in the Range ref and use it in a few different ways. I appreciate the time.

Regards, Ramses

 

[JoeMo]

JoeMo - thanks for the reply. Generic code will do (if I know where to put it !)

My list is called "StaffList" and there are a few ranges that I will want to check - my sheets are just sheet1 to sheet6 etc.

Thanks for the help, I am interested in learning something at the same time if I can.

If you want to use event code (i.e. worksheet_change) you could do so by calling the code Domenic has provided. Something like this (untested):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("StaffList")) Is Nothing Then
    Application.EnableEvents = False
    Call ClearOldItemsFromCells
    Application.EnableEvents = True
End If
End Sub

Be sure to change the named range and the range to clear in Domenic's code to match your equivalents.

EDIT: I'm assuming your named range is scoped to workbook level.

 

[Rick Rothstein]

Here is another macro for you to consider... it will process all sheets from Sheet1 to Sheet6 automatically. Assuming your Validation formula for the Validation List is "=StaffList", then this macro will do what you asked for...

[table="width: 500"]
[tr]
	[td]Sub UpdateSelectionsFromChangedDataList()
  Dim X As Long, Cnt As Long, Cell As Range, VC As Range
  For X = 1 To 6
    Set VC = Nothing
    On Error Resume Next
    Set VC = Sheets("Sheet" & X).Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If Not VC Is Nothing Then
      For Each Cell In VC
        If Cell.Validation.Formula1 = "[COLOR="#0000FF"]=StaffList[/COLOR]" Then
          If Range("StaffList").Find(Cell.Value, , xlValues, xlWhole) Is Nothing And Cell.Value <> "" Then
            Cnt = Cnt + 1
            Cell.ClearContents
          End If
        End If
      Next
    End If
  Next
  MsgBox "Total number of cells cleared on all sheets: " & Cnt, vbInformation
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Here is another macro for you to consider... it will process all sheets from Sheet1 to Sheet6 automatically. Assuming your Validation formula for the Validation List is "=StaffList", then this macro will do what you asked for...

[table="width: 500"]
[tr]
	[td]Sub UpdateSelectionsFromChangedDataList()
  Dim X As Long, Cnt As Long, Cell As Range, VC As Range
  For X = 1 To 6
[B][COLOR="#FF0000"]    Set VC = Nothing[/COLOR][/B]
    On Error Resume Next
    Set VC = Sheets("Sheet" & X).Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If Not VC Is Nothing Then
      For Each Cell In VC
        If Cell.Validation.Formula1 = "[COLOR="#0000FF"][B]=StaffList[/B][/COLOR]" Then
          If Range("StaffList").Find(Cell.Value, , xlValues, xlWhole) Is Nothing And Cell.Value <> "" Then
            Cnt = Cnt + 1
            Cell.ClearContents
          End If
        End If
      Next
    End If
  Next
  MsgBox "Total number of cells cleared on all sheets: " & Cnt, vbInformation
End Sub[/td]
[/tr]
[/table]

Actually, you can remove the line of code I highlighted in red above. I was running into an error during my testing of the code for what turned out to be a different reason than I first thought, so I put that line of code in thinking it would solve what I thought was the problem and forgot to remove it once I "stumbled" onto what was actually causing the problem.

 

[Rick Rothstein]

I have modified the code for the macro I posted earlier once more in order to simplify its use. You no longer have to specify what sheets to process any more, the code takes care of everything automatically. I simply loop through all the sheets in the active workbook and do a quick test to see if any cells have any Data Validation on them... if they don't, the sheet is skipped... if they do, the code starts by trying to iterate each cell with Data Validation in it and a fast test performed to see if its Data Validation formula is "=StaffList"... when a Data Validation cell is found with that Data Validation formula, each cell on the worksheet (no matter where is located) with at same Data Validation formula, and only those cells, are iterated after which the code skips the remainder of the Data Validation cells it did not yet visit and skips over to the next sheet instead. All in all, this turns out to actually be a very fast process.

[table="width: 500"]
[tr]
	[td]Sub UpdateSelectionsFromChangedDataList()
  Dim X As Long, Cnt As Long, Cell As Range, VC As Range, VCList As Range, VCListCell As Range, WS As Worksheet
  For Each WS In Worksheets
    Set VC = Nothing
    On Error Resume Next
    Set VC = WS.Cells.SpecialCells(xlCellTypeAllValidation)
    On Error GoTo 0
    If Not VC Is Nothing Then
      For Each Cell In VC
        If Cell.Validation.Formula1 = "=StaffList" Then
          Set VCList = Cell.SpecialCells(xlCellTypeSameValidation)
          For Each VCListCell In VCList
            If Range("StaffList").Find(VCListCell.Value, , xlValues, xlWhole) Is Nothing And VCListCell.Value <> "" Then
              Cnt = Cnt + 1
              VCListCell.ClearContents
            End If
          Next
          Exit For
        End If
      Next
    End If
  Next
  MsgBox "Number of cells cleared: " & Cnt, vbInformation
End Sub[/td]
[/tr]
[/table]

 

[Ramses505]

Rick, Thanks ever so much for putting so much time and effort into this, I appreciate it. I will be porting it into my sheets on Monday and will report back with any further questions. Thanks again.