Programmatically creating an Excel VBA validation list

Question

I have a data array that goes into VBA code from an external source. I want to be able to assign this data for use as a check in the drop-down list in a cell on one of the sheets of this book. However, I do not want to copy this data into a worksheet, and then use a named range - there can be quite a lot of data, and it will not be very efficient!

I am sure there must be a way, but I have not found it yet. Any ideas?

Answer 1

• Put the data in some text file, delimiting it with a comma, for example (a, b, c).

• VBA , ValidationList.

• -

  ( "A1" ).  .Add : = xlValidateList, AlertStyle: = xlValidAlertStop, Operator: = _
   xlBetween, Formula1: = ValidationList
   .IgnoreBlank = True
   .InCellDropdown = True
   .Inputtitle=""
   .Errortitle=""
   .InputMessage = ""
   .ErrorMessage = ""
   .ShowInput = True
   .ShowError = True
  

Answer 2

, , "" 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

, , :)