Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB syntax to test for data validation in a cell
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB syntax to test for data validation in a cell
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB syntax to test for data validation in a cell
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Test in Validation Box Very Small Font Size... | Excel Discussion (Misc queries) | |||
Data Validation: Store cell address instead of value in the cell? | Excel Discussion (Misc queries) | |||
Why does data validation not work when pasting data into a cell. | Excel Discussion (Misc queries) | |||
Data Validation Syntax Question | Excel Discussion (Misc queries) |