Using comma inside the comma delimited text in Data Validation/Sou

[LasseH]

This works fine:
Selection.Validation.Add xlValidateList, , , "10000,20000,30000"

How do I use comma INSIDE my items in the list, fx. if I want the list to
contain:
10,000
20,000
30,000

One would assume that the following would do the trick, but it doesn't:
Selection.Validation.Add xlValidateList, , , "'10,000','20,000','30,000'"

Any idea on how to do this?

 

[Smallweed]

Don't think it's possible with straight text - the only way I can see is to
use a worksheet reference (e.g. "=$AA$1:$AA$3"), enter the values in this
range and format them accordingly.

If you don't like this text way of expressing the range, you can fiddle it
with:
"=" & Range("$AA$1:$AA$3").Address
or some such.

 

[Tim Williams]

sVal="10" & Chr(130) & "000,20" & Chr(130) & "000"

Selection.Validation.Add xlValidateList, , , sVal

Tim

 

[LasseH]

I could of course make an invisible sheet, and make a "dummy reference", or I
could find a cell in the active sheet which is not used and use that as a
"dummy reference", but I would prefer a nicer way.

 

[LasseH]

Hi Tim,

Thank you, this would work ...until the user picks an item in the list and
the content of the cell will be some text containing the ascii130 character
in stead of the number 10.000, which Excel will not be able to fx. subtract
from another number.

Any other ideas? I just want a combobox in a cell, it do not neccesarily
have to be Validation object. I prefer not to use an ActiveX Combobox object,
though.


/Lasse

 

[Tim Williams]

If you just want numbers to be selected, just have numbers in the list.
There's no way to "format" the list.
If you really want "commas" then you could use intermediate formulas to
strip them out for calculations.

Tim