Excel VBA read validation list into variable

Question

I was trying to find an Excel VBA code that reads validation list into a variable.

I'm using Excel 2010 version.

In the code below I'm dynamically setting up values using variable qNR, and appears in dropdown menu. Later I need to read values in validation list, remove or update certain values and set new values.

I cannot find any example code that reads validation list into a variable.

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=qNR
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True

End With

Thank you!

Validation lists can't do all the things you would expect from a ListBox. Trying to make an Excel sheet function as a form is a frustrating experience. Use WinForms where you can. Maybe use Access instead. — HackSlash
– HackSlash, Commented Nov 6, 2018 at 17:02

Aaron Thomas · Accepted Answer · 2013-09-19 11:39:45Z

2

Assuming it's a list type (validation type 3), then Debug.Print Range(yourRange).Validation.Formula1 should print out a list. So Object.Validation.Formula1 would appear to work for you. See here for more info.

answered Sep 19, 2013 at 11:39

Aaron Thomas

5,3418 gold badges52 silver badges94 bronze badges

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

3 Comments

malas Over a year ago

Worked using Target.Validation.Formula1. Thanks you very much Aaron

Argut Over a year ago

Object.Validation.Formula1 returns a formula associated with a Validation object. @malas, how do you print the list? What is Target?

thanos.a Over a year ago

@Aaron Thomas Both links are broken

Collectives™ on Stack Overflow

Excel VBA read validation list into variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related