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:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValCell
End With
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
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$1:$A$3" 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!