Data Validation problem.

daveo61 · #1 (**permalink**) 10-24-05, 06:29

I'm building a comma delimited string through VBA to add a data validation list to a cell.

However, I can only seem to get a max of 255 characters to be recognised and populate into the list.

Any ideas of how to get around this at all please, or any advise on how to get the list to populate wioth more characters.

TIA.

rockstar · #2 (**permalink**) 10-24-05, 14:30

I do believe 255 is a maximum. It is a limitation in Excel.

savbill · #3 (**permalink**) 10-24-05, 20:20

Quote:

Originally Posted by daveo61

I'm building a comma delimited string through VBA to add a data validation list to a cell.

Split out your CSV values into a column on your worksheet. Then use a lookup formula or '=COUNTIF(A:A,"fa")' to check for a matching value in the column. Use Split Function to extract CSV to an Array.

Code:

Sub TestArray()
Dim strCSV As String

strCSV = "doo,rae,me,fa,so,lay,de,doe"

arySubList = fSplitStr(strCSV)
  
  For i = 0 To UBound(arySubList)
    nRow = i + 2
    Cells(nRow, 1) = arySubList(i)
  Next i

End Sub

Private Function fSplitStr(strVal As String)
  If InStr(strVal, ",") > 0 Then
      fSplitStr = Split(strVal, ",", , vbTextCompare)
  End If
End Function

daveo61 · #4 (**permalink**) 10-25-05, 03:19

Cheers for the ideas guys, most helpful!

Just in case others may look at this post here's another way to do it.

My preferred route to have done this would have been by declaring a range value as the list's source, but as this range was on a different sheet I wasn't able to do it.

I subsequently found out that if you name the range on the sheet, then you can reference this directly in the VBA.

The code now looks a little like this (Please bear in mind I need to create multiple lists and their dynamic in so much as based on which sheet is selected. MailPack is the range and 4 relates to column 'D' as this is where I want it.)

Sheet that calls the drop down function...

Code:

Call cdDropDowns.cdAddDropDown("MailPack", Target.Row, 4)

Module that handles the list...

Code:

Sub cdAddDropDown(strWhat As String, intRow As Integer, intColumn As Integer)

Cells(intRow, intColumn).Select

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=" & strWhat
End With

End Sub

Hope this could be of future use to others.

Hope this may help.

Internet Services

Web Development

Digital Marketing

Consumer Tech