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

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?

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

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.


"LasseH" wrote:

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?

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

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

Selection.Validation.Add xlValidateList, , , sVal

Tim

"LasseH" wrote in message
...
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?

Using comma inside the comma delimited text in Data Validation
 

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.



"Smallweed" wrote:

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.


"LasseH" wrote:

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?

Using comma inside the comma delimited text in Data Validation
 

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" wrote:

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

Selection.Validation.Add xlValidateList, , , sVal

Tim

"LasseH" wrote in message
...
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?

Using comma inside the comma delimited text in Data Validation
 

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

"LasseH" wrote in message
...
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" wrote:

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

Selection.Validation.Add xlValidateList, , , sVal

Tim

"LasseH" wrote in message
...
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?