开发者

Programmatically creating validation list

开发者 https://www.devze.com 2023-01-31 19:23 出处:网络
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

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. A开发者_Python百科ny ideas?


  1. Place the data in some text file delimiting it with comma eg(a,b,c).

  2. Read that data using VBA into a string variable eg ValidationList.

  3. 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


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 :)

0

精彩评论

暂无评论...
验证码 换一张
取 消