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
– Rory, Commented Jul 22, 2014 at 16:03
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
– mrbungle, Commented Jul 22, 2014 at 16:04
@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
– Doug Glancy, Commented Jul 22, 2014 at 16:08

Rory · Accepted Answer · 2014-07-22 16:20:26Z

1

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.

answered Jul 22, 2014 at 16:20

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Data Validation Drop Down Box

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related