| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validation list with comma values?
Hello,
I am trying to create programmatically a validation list that contains comma values and I cant get it to work. In Excels validation dialog I can manually enter the formula in the filed which works fine (; separated list for example: 1,0;1,2 ...) I if enter this via VBA as Formula1 "1,0;1,2" things get messed up: 1. Value: 1 2. Value: 0;1 3. Value: 2 The Problem is that the values are calculated at runtime so I can't enter them in the dialog and the list is needed because the user will only known the approximate and not the exact values which can be entered and they will have some more decimal places. Last but not least an analyzing software which runs over the sheet doesn't allow that there are any hidden columns or values so I can't just make an reference to a range which contains the values... Now the question is, if is this possible to do or has Microsoft totally messed up the validation.add function?? Any help is appreciated! Kind regards, Sebastian |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validation list with comma values?
I think you were hit by VBA being USA centric.
If I enter 1,0;1,2 directly in the data|validation dialog (with comma my list separator), I get what you get from your code. Can you put the values in a range on a hidden worksheet and use them from there? Can you use a different character than comma? 1-0,1-2,1-3... Maybe use another cell to change the dash back to comma if you really need it: =substitute(a1,"-",",") Sebastian wrote: Hello, I am trying to create programmatically a validation list that contains comma values and I cant get it to work. In Excels validation dialog I can manually enter the formula in the filed which works fine (; separated list for example: 1,0;1,2 ...) I if enter this via VBA as Formula1 "1,0;1,2" things get messed up: 1. Value: 1 2. Value: 0;1 3. Value: 2 The Problem is that the values are calculated at runtime so I can't enter them in the dialog and the list is needed because the user will only known the approximate and not the exact values which can be entered and they will have some more decimal places. Last but not least an analyzing software which runs over the sheet doesn't allow that there are any hidden columns or values so I can't just make an reference to a range which contains the values... Now the question is, if is this possible to do or has Microsoft totally messed up the validation.add function?? Any help is appreciated! Kind regards, Sebastian -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validation list with comma values?
Thank your for your reply.
I can't use another separator because this value will be used in another formula :-/ And as far as I know and just tested, you can't assign a range to the validate property that is on another sheet :-( "Dave Peterson" wrote: I think you were hit by VBA being USA centric. If I enter 1,0;1,2 directly in the data|validation dialog (with comma my list separator), I get what you get from your code. Can you put the values in a range on a hidden worksheet and use them from there? Can you use a different character than comma? 1-0,1-2,1-3... Maybe use another cell to change the dash back to comma if you really need it: =substitute(a1,"-",",") Sebastian wrote: Hello, I am trying to create programmatically a validation list that contains comma values and I can€„¢t get it to work. In Excels validation dialog I can manually enter the formula in the filed which works fine (; separated list for example: 1,0;1,2 ...) I if enter this via VBA as Formula1 "1,0;1,2" things get messed up: 1. Value: 1 2. Value: 0;1 3. Value: 2 The Problem is that the values are calculated at runtime so I can't enter them in the dialog and the list is needed because the user will only known the approximate and not the exact values which can be entered and they will have some more decimal places. Last but not least an analyzing software which runs over the sheet doesn't allow that there are any hidden columns or values so I can't just make an reference to a range which contains the values... Now the question is, if is this possible to do or has Microsoft totally messed up the validation.add function?? Any help is appreciated! Kind regards, Sebastian -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validation list with comma values?
Hi Sebastian.
And as far as I know and just tested, you can't assign a range to the validate property that is on another sheet :-( Try naming the remote range and use the name in the DV list box, e,g.: =MyList --- Regards, Norman "Sebastian" wrote in message ... Thank your for your reply. I can't use another separator because this value will be used in another formula :-/ And as far as I know and just tested, you can't assign a range to the validate property that is on another sheet :-( "Dave Peterson" wrote: I think you were hit by VBA being USA centric. If I enter 1,0;1,2 directly in the data|validation dialog (with comma my list separator), I get what you get from your code. Can you put the values in a range on a hidden worksheet and use them from there? Can you use a different character than comma? 1-0,1-2,1-3... Maybe use another cell to change the dash back to comma if you really need it: =substitute(a1,"-",",") Sebastian wrote: Hello, I am trying to create programmatically a validation list that contains comma values and I can?Tt get it to work. In Excels validation dialog I can manually enter the formula in the filed which works fine (; separated list for example: 1,0;1,2 ...) I if enter this via VBA as Formula1 "1,0;1,2" things get messed up: 1. Value: 1 2. Value: 0;1 3. Value: 2 The Problem is that the values are calculated at runtime so I can't enter them in the dialog and the list is needed because the user will only known the approximate and not the exact values which can be entered and they will have some more decimal places. Last but not least an analyzing software which runs over the sheet doesn't allow that there are any hidden columns or values so I can't just make an reference to a range which contains the values... Now the question is, if is this possible to do or has Microsoft totally messed up the validation.add function?? Any help is appreciated! Kind regards, Sebastian -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validation list with comma values?
Thnak you very much, this really works!
Anyway this is a solution I can live with, calculating the data on another sheet as temporary storage... ;-) "Norman Jones" wrote: Hi Sebastian. And as far as I know and just tested, you can't assign a range to the validate property that is on another sheet :-( Try naming the remote range and use the name in the DV list box, e,g.: =MyList --- Regards, Norman "Sebastian" wrote in message ... Thank your for your reply. I can't use another separator because this value will be used in another formula :-/ And as far as I know and just tested, you can't assign a range to the validate property that is on another sheet :-( "Dave Peterson" wrote: I think you were hit by VBA being USA centric. If I enter 1,0;1,2 directly in the data|validation dialog (with comma my list separator), I get what you get from your code. Can you put the values in a range on a hidden worksheet and use them from there? Can you use a different character than comma? 1-0,1-2,1-3... Maybe use another cell to change the dash back to comma if you really need it: =substitute(a1,"-",",") Sebastian wrote: Hello, I am trying to create programmatically a validation list that contains comma values and I canâ?Tt get it to work. In Excels validation dialog I can manually enter the formula in the filed which works fine (; separated list for example: 1,0;1,2 ...) I if enter this via VBA as Formula1 "1,0;1,2" things get messed up: 1. Value: 1 2. Value: 0;1 3. Value: 2 The Problem is that the values are calculated at runtime so I can't enter them in the dialog and the list is needed because the user will only known the approximate and not the exact values which can be entered and they will have some more decimal places. Last but not least an analyzing software which runs over the sheet doesn't allow that there are any hidden columns or values so I can't just make an reference to a range which contains the values... Now the question is, if is this possible to do or has Microsoft totally messed up the validation.add function?? Any help is appreciated! Kind regards, Sebastian -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation - List of unique values | Excel Discussion (Misc queries) | |||
| How do I add an item with comma in the list of data validation | Excel Discussion (Misc queries) | |||
| How to update edited values in validation list? | Excel Discussion (Misc queries) | |||
| List cell values seperated by comma if criteria met | Excel Worksheet Functions | |||
| Show comma in data validation list? | Excel Discussion (Misc queries) | |||
Linear Mode
