Need help with Excel Validation Lists

  • Thread starter Thread starter sharad.srivastava
  • Start date Start date
S

sharad.srivastava

Hi ,
I am creating a validation list using VB macros using the
following code snippet -

--------------------------------------------------------------------------
ov.Validation.Delete
ov.Validation.Add Type:=xlValidateList, Formula1:="1-Poor, improvement
needed ,2-Average,3-Good, Nice work"
----------------------------------------------------------------------------

The validation list that is created shows the following list elements
1-Poor
improvement needed
2-Average
3-Good
Nice work


What I require is that the comma in between the string should not be
taken as a delimiter, that is
the list elements should be
1-Poor, improvement needed
2-Average
3-Good, Nice work

I am unable to find any escape character that can suppress the
delimiting comma.
Is there any way this can be done.

TIA
Sharad
 
You could put the values in a range, Debra Dalgleish shows how:http://contextures.com/xlDataVal01.html#Name

Or you could get rid of the commas (maybe use a couple of dashes???) if you want
to use a list.
Click to expand...

Thanks Dave, I tried both your options, but I cannot implement any of
them.

I cannot replace a comma by any other character as this would not be
an acceptable UI change.
The other option of putting the values in a range is very tedious, as
this has to be done dynamically -> So the string has to be parsed,
value copied to another cell, then a range should be created from
these cells and returned. Then this has to be done across sheets and
that can get dirty.

A more feasible solution for me would be a escape character or a
validation formula.
Any pointers on this one.????
 
Maybe chr(130) would work:

Formula1:="1-Poor" & Chr(130) & _
" improvement needed ,2-Average,3-Good" _
& Chr(130) & " Nice work"
 
Use chr(130) to replace comma

The solution is to replace the standard comma with its cousin, chr(130).
Before you build your list, modify each member. Use this code, for example:

mystring = replace(mystring, ",", chr(130))

mylist = mylist & mystring & ","
 
Back
Top