Need help with Excel Validation Lists

  • Thread starter sharad.srivastava
  • 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
 
S

sharad.srivastava

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.????
 
D

Dave Peterson

Then I don't think it's possible. There's no escape character that I know.
 
D

Debra Dalgleish

Maybe chr(130) would work:

Formula1:="1-Poor" & Chr(130) & _
" improvement needed ,2-Average,3-Good" _
& Chr(130) & " Nice work"
 
Top