Add addresses of all Data Validation cells in column into an Array

Question

I have a worksheet Change event macro so that whenever a data validation cell's value is "" (delete key hit, etc.), it pastes in the string "Click to Enter Value". Code below and it's working fine.

On a different sheet I need the same functionality, but applied to multiple data validation cells, all in one column, though very spread out. So, my VBA need is to alter the Change Event macro to now look for changes in only these validation cells.

Initial thinking is to put all data validation cell addresses into an array for the macro to cycle through at each change, checking for the blank value. Is this the most efficient way? If so, I need help with the syntax. Say the cells are all in column C...

Here's the one-cell macro from the first sheet. Thank you for ANY guidance.:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Range("B2").Value = "" Then
     Range("B2").Value = "Click to Enter"
  End If
End Sub

Unknown Unknown · Accepted Answer · 2018-06-07T22:55:01

The SpecialCells method has xlCellTypeAllValidation.

Private Sub Worksheet_Change(ByVal Target As Range)
  on error goto safe_exit
  If not intersect(target, target.parent.Cells.specialcells(xlCellTypeAllValidation)) is nothing then
     application.enableevents = false
     dim t as range
     for each t in intersect(target, target.parent.Cells.specialcells(xlCellTypeAllValidation))
         if not cbool(len(t.value)) then _
             t.Value = "Click to Enter"
     next t
  End If
safe_exit:
    application.enableevents = true
End Sub

Add addresses of all Data Validation cells in column into an Array

1 Answers