Need help with Excel Validation Lists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sharad.srivastava@gmail.com - 02 Apr 2007 15:31 GMT

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

Reply to this Message

Dave Peterson - 02 Apr 2007 16:23 GMT

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.

> Hi ,
> I am creating a validation list using VB macros using the
[quoted text clipped - 26 lines]
> TIA
> Sharad

Signature

Dave Peterson

Reply to this Message

sharad.srivastava@gmail.com - 03 Apr 2007 06:13 GMT

> You could put the values in a range, Debra Dalgleish shows how:http://contextures.com/xlDataVal01.html#Name
>
[quoted text clipped - 37 lines]
>
> Dave Peterson

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

Reply to this Message

Dave Peterson - 03 Apr 2007 13:42 GMT

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

> > You could put the values in a range, Debra Dalgleish shows how:http://contextures.com/xlDataVal01.html#Name
> >
[quoted text clipped - 52 lines]
> validation formula.
> Any pointers on this one.????

Signature

Dave Peterson

Reply to this Message

Debra Dalgleish - 03 Apr 2007 15:24 GMT

Maybe chr(130) would work:

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

> Then I don't think it's possible. There's no escape character that I know.
>
[quoted text clipped - 54 lines]
>>validation formula.
>>Any pointers on this one.????

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Rate this thread: