Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005

    Unanswered: Data Validation problem.

    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.


  2. #2
    Join Date
    Oct 2005
    I do believe 255 is a maximum. It is a limitation in Excel.

  3. #3
    Join Date
    Feb 2004
    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.
    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


  4. #4
    Join Date
    Feb 2005
    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...

    Call cdDropDowns.cdAddDropDown("MailPack", Target.Row, 4)
    Module that handles the list...

    Sub cdAddDropDown(strWhat As String, intRow As Integer, intColumn As Integer)
    Cells(intRow, intColumn).Select
    With Selection.Validation
        .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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts