Converting Criteria Listed in Excel For Data Validation

[Lynn]

I am having problems with creating a dropbox with the data validation. I have 120 criteria sets, which described in one cell. For example, Cell A1 contains "x,y,Max(x,y)". When I use the following code:
[VBA]
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValCell
End With
[/VBA]This makes 4 choices in the dropbox: x, y, Max(x, and y) where there should be only one. Is there anyone else who had to do it? I am having a really hard time. Thanks!

[mikerickson]

What did you want the list to be?

Would putting "x" in A1, "y" in A2, "Max(x,y)" in A3 and
[VBA].Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$1:$A$3"[/VBA] do what you want?

[Lynn]

The criteria is in one cell and I have to keep it that way since someone else is populating the criteria list. I can't convert the text to columns. Is there a way to that in VBA?

[Bob Phillips]

You have two major difficulties, first the value will be seen as as string, not a formula (as I assume the max to be), and you have a delimiter (,) embedded within that formula.

If you were to do it in VBA I think it would have to be parsed bit by bit, and it would need a lot of rules and processing code. Too hard methinks.

[mikerickson]

This should do what you need it to do. Where supported, a scripting dictionary might be used instead of the loop.

With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
        Formula1:=SpecialCommasInsideParenthesis(valCell.Value)
End With

Function SpecialCommasInsideParenthesis(inputString As String) As String
Dim i As Long, oneChr As String, workingInside As Long
    For i = 1 To Len(inputString)
        oneChr = Mid(inputString, i, 1)
        If oneChr = "," And CBool(workingInside) Then
            oneChr = Chr(226)
        End If
        SpecialCommasInsideParenthesis = SpecialCommasInsideParenthesis & oneChr
        workingInside = workingInside - (oneChr = "(") + (oneChr = ")")
    Next i
End Function

[Lynn]

Thanks, Mike. That's exactly that I was looking for. Cheers!