Excel Data Validation Drop Down Box

Question

I have a question that does not require any code. Let's say in Excel you set a data validation on a cell, and insert a drop down of specific values for the user to select from that cell. Let's say also that in VBA you are designating that cell a value from a DB. If the value from the DB does not match any of the values you have designated in the drop-down, will it populate the value in the cell? Or will it just leave it blank? Does anyone have experience with this?

Code will ignore the DV settings and simply populate it anyway. If you need to test afterwards whether it's valid data, check the Validation.Value and see if it's True. — Rory
Just from a quick test I was able to change the value of the cell to something outside the data validation. But if you're already in VBA why not add the data validation progrmatically? — mrbungle
@Rory, your comment seems worthy of an answer. I'd be interested in an expanded version, as I haven't ever used Validation.Value. — Doug Glancy

Rory Rory · Accepted Answer · 2014-07-22T16:20:26

Code will ignore the DV settings and simply populate it anyway. If you need to test afterwards whether it's valid data, check the Validation.Value and see if it's True:

With Range("T1")
    .Value = "maybe"
    If .Validation.Value Then
        MsgBox "Valid entry"
    Else
        MsgBox "Invalid entry"
        .ClearContents
    End If
End With

for example.

Excel Data Validation Drop Down Box

1 Answers