VB syntax to test for data validation in a cell

[Dave O]

I have inherited a workbook of questionable design- many named ranges,
for instance, that are used by dropdowns for data validation. I'd like
to go cell by cell and determine if data validation is used in that
cell, and if yes what named range is referenced.

I could make this work if the following code would run, but it
generates an error. This is the kind of cell-by-cell test I'd like to
perform. Can anyone suggest better syntax? Thanks!

Sub test()
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange
If rCell.Validation.InCellDropdown = True Then 'THIS LINE GENERATES
THE ERROR
MsgBox rCell.Address
End If
Next rCell

End Sub

 

[Bob Phillips]

Nothing wrong with defined names!

Public Sub test()
Dim rCell As Range
Dim dvType

For Each rCell In ActiveSheet.UsedRange
dvType = 0
On Error Resume Next
dvType = rCell.Validation.Type
On Error GoTo 0
If dvType <> 0 Then MsgBox rCell.Address
Next rCell

End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave O]

Thanks, Bob- you're right, nothing wrong with defined names: I'm just
trying to delete the redundant ones. I had an On Error Resume Next
line, but not the On Error GoTo 0. Thanks for the tip!