VBA Use Array Values As Cell Validation Values

Question

What I am doing now,

Populate an array with unique data using a particular column (Item column) from multiple sheets
Populate a dropdown list in a different sheet using above array values

However, I have an error on the validation step, where I try to set Formula1:=arrItems()

Here is my code:

    Dim ws As Worksheet
    Dim tmpItems As String
    Dim arrItems() As String
    Dim tmpDates As String
    Dim arrDates() As String
    Dim ItemColumn As Range
    Const ItemHeaderCell As String = "Item"
    Dim EmptyRange As Range

    For Each ws In ActiveWorkbook.Worksheets
      If ws.Name <> "Raw Data" Then
        ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes).Name = ws.Name
        ws.ListObjects(ws.Name).TableStyle = "TableStyleMedium9"
        tmpDates = tmpDates & ws.Name & "|"
        Set ItemColumn = ws.UsedRange.Find(ItemHeaderCell, , xlValues, xlWhole)

        For Each EmptyRange In Intersect(ItemColumn.EntireColumn, ws.UsedRange).Cells
        'skip the header and empty cells
          If Not IsEmpty(EmptyRange.Value) And EmptyRange.Address <> ItemColumn.Address 
        And (InStr(tmpItems, EmptyRange) = 0) Then
            tmpItems = tmpItems & EmptyRange.Value & "|"
          End If
        Next EmptyRange
      End If
    Next ws

    If Len(tmpItems) > 0 Then 
       tmpItems = Left(tmpItems, Len(tmpItems) - 1)
       arrItems = Split(tmpItems, "|")

       If Len(tmpDates) > 0 Then 
         tmpDates = Left(tmpDates, Len(tmpDates) - 1)
         arrDates = Split(tmpDates, "|")

         Dim worksheet2 As Worksheet
         Set worksheet2 = ActiveWorkbook.Sheets.Add(Before:=ActiveWorkbook.Sheets(1))
         worksheet2.Name = "Main"

         With worksheet2.Range("A1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=arrItems()
         End With

How to rectify this error?

Just a suggestion only, If you could update your post which works like a charm, it will benefit others. Your question is a good one and deserves more credit. — skkakkar
– skkakkar, Commented Jul 9, 2016 at 5:01

Doug Glancy · Accepted Answer · 2015-07-09 01:48:03Z

1

Validation.Formula1 is a string, not an array. If I"m reading your code correctly you could change the line that populates tmpItems to:

tmpItems = tmpItems & EmptyRange.Value & ","

After that just get rid of the last comma and use tmpItems for your Formula1.

answered Jul 9, 2015 at 1:48

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

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

3 Comments

Summer Developer Over a year ago

So, the validation will read the dropdown as one item per each comma separated item?

Doug Glancy Over a year ago

Yup. A good way to see this is to record a macro where you type in a couple of items.

Rory Over a year ago

Just remember you're limited to 255 characters with a literal list like this.

Collectives™ on Stack Overflow

VBA Use Array Values As Cell Validation Values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related