Programmatically creating validation list

Question

I have an array of data that is coming in to the VBA code from an external source. I want to be able to assign that data to use as a validation in a dropdown box in a cell in one of the sheets in this workbook. However, I do not want to copy that data into a sheet and then use a named range - there may be quite a lot of data, and that would not feel very efficient!

I'm sure there must be a way - but I haven't found one yet. Any ideas?

Thunder · Accepted Answer · 2010-12-21 10:05:30Z

4

Place the data in some text file delimiting it with comma eg(a,b,c).
Read that data using VBA into a string variable eg ValidationList.
Use some thing like this

With Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

edited Dec 21, 2010 at 10:05

answered Dec 21, 2010 at 9:57

Thunder

11.1k27 gold badges90 silver badges119 bronze badges

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

4 Comments

Thunder Over a year ago

Here replace "A1" with the cell you want to validate.

Alistair Collins Over a year ago

Thanks Thunder for the quick and accurate answer. The only issue with it is having to change the array into a comma separated string, which makes it a little more wordy. However it does what I need - thanks!

Alistair Collins Over a year ago

It may also be useful to add the line .Delete after With Range("A1").Validation to remove any previous validation in that cell, rather than cause an error.

Noah Over a year ago

This works on Excel 2007 for me, but not 2003. I can't believe it is that recent of a feature - is there something I am missing?

Kymon Lown · Accepted Answer · 2013-07-04 11:45:00Z

0

Here's a little trick I used, in this "list" is an ArrayList:

Dim ValidateList As String
For Each x In list
ValidateList = ValidateList + x + Chr(44)
Next
 With Sheets(yoursheet).Range(yourCell).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
     xlBetween, Formula1:=ValidateList
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Might seem a bit crude, but I think it works without any problems :)

edited Jul 4, 2013 at 11:45

answered Jul 3, 2013 at 14:11

Kymon Lown

34 bronze badges

Collectives™ on Stack Overflow

Programmatically creating validation list

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related