Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Validation formula1:=Join(... Is '!' a reserved cha
Greetings,
I've written this question twice, and was about to abort it for the second time, but will post it for comments, even though the problem may be solved. Note that below, the first string element of gNameAbbrs() had a leading '!' (exclamation mark), and removing it _seems_ to have made a difference!(?) (the original question) I don't understand why the the code below doesn't work. After it executes, the affected cell does not allow for a pull-down list - even though the list DOES appear in Data\Validation dialog for the cell! Excel (2002) also starts acting strange and will no longer quit via File\Exit Or the window-close button - in either case a pop-up dialog announces that Excel can't be exited. Killing Excel via TaskMgr is effective. I've quintuple-checked the gNameAbbrs array (of Strings) is correctly populated, again, the values ARE showing-up in Data\Validation UI. Code:
rRange.Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(gNameAbbrs, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Validation formula1:=Join(... Is '!' a reserved cha
Hi
I tried to run the code below in both excel 2000 and 2007, with no errors. Have you tried to move your code to a new workbook, to test if it also happens there. Maybe your current workbook is somehow corrupted! Sub test() Dim MyArr(0 To 15) As String For c = 0 To 15 MyArr(c) = "! A" & c Next MyString = Join(MyArr, ",") With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(MyArr, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With End Sub Regards, Per "tbd" skrev i meddelelsen ... Greetings, I've written this question twice, and was about to abort it for the second time, but will post it for comments, even though the problem may be solved. Note that below, the first string element of gNameAbbrs() had a leading '!' (exclamation mark), and removing it _seems_ to have made a difference!(?) (the original question) I don't understand why the the code below doesn't work. After it executes, the affected cell does not allow for a pull-down list - even though the list DOES appear in Data\Validation dialog for the cell! Excel (2002) also starts acting strange and will no longer quit via File\Exit Or the window-close button - in either case a pop-up dialog announces that Excel can't be exited. Killing Excel via TaskMgr is effective. I've quintuple-checked the gNameAbbrs array (of Strings) is correctly populated, again, the values ARE showing-up in Data\Validation UI. Code:
rRange.Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(gNameAbbrs, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Validation formula1:=Join(... Is '!' reserved?
Hi Per Jessen,
Thanks for testing the code! Your work gives me greater confidence that the actual problem was a special character in one of the list-items. My pull-down list is file-names, and the first element of gNameAbbr() had a leading '!'. Many thanks, Cheers! "Per Jessen" wrote: Hi I tried to run the code below in both excel 2000 and 2007, with no errors. Have you tried to move your code to a new workbook, to test if it also happens there. Maybe your current workbook is somehow corrupted! Sub test() Dim MyArr(0 To 15) As String For c = 0 To 15 MyArr(c) = "! A" & c Next MyString = Join(MyArr, ",") With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(MyArr, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With End Sub Regards, Per "tbd" skrev i meddelelsen ... Greetings, I've written this question twice, and was about to abort it for the second time, but will post it for comments, even though the problem may be solved. Note that below, the first string element of gNameAbbrs() had a leading '!' (exclamation mark), and removing it _seems_ to have made a difference!(?) (the original question) I don't understand why the the code below doesn't work. After it executes, the affected cell does not allow for a pull-down list - even though the list DOES appear in Data\Validation dialog for the cell! Excel (2002) also starts acting strange and will no longer quit via File\Exit Or the window-close button - in either case a pop-up dialog announces that Excel can't be exited. Killing Excel via TaskMgr is effective. I've quintuple-checked the gNameAbbrs array (of Strings) is correctly populated, again, the values ARE showing-up in Data\Validation UI. Code:
rRange.Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(gNameAbbrs, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Validation formula1:=Join(... Is '!' a reserved
Hi Per Jessen,
That's what I get for not reading carefully - looks like you also have a leading '!' in your list... Now I'm really stumped. I guess if it starts happening again, I'll be back! Thanks/Cheers! "Per Jessen" wrote: Hi I tried to run the code below in both excel 2000 and 2007, with no errors. Have you tried to move your code to a new workbook, to test if it also happens there. Maybe your current workbook is somehow corrupted! Sub test() Dim MyArr(0 To 15) As String For c = 0 To 15 MyArr(c) = "! A" & c Next MyString = Join(MyArr, ",") With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(MyArr, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With End Sub Regards, Per "tbd" skrev i meddelelsen ... Greetings, I've written this question twice, and was about to abort it for the second time, but will post it for comments, even though the problem may be solved. Note that below, the first string element of gNameAbbrs() had a leading '!' (exclamation mark), and removing it _seems_ to have made a difference!(?) (the original question) I don't understand why the the code below doesn't work. After it executes, the affected cell does not allow for a pull-down list - even though the list DOES appear in Data\Validation dialog for the cell! Excel (2002) also starts acting strange and will no longer quit via File\Exit Or the window-close button - in either case a pop-up dialog announces that Excel can't be exited. Killing Excel via TaskMgr is effective. I've quintuple-checked the gNameAbbrs array (of Strings) is correctly populated, again, the values ARE showing-up in Data\Validation UI. Code:
rRange.Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=Join(gNameAbbrs, ",") 'this isn't working 'xlBetween , Formula1:="a,b,c" 'this works .InCellDropdown = True .InputMessage = "" End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation trouble | Excel Programming | |||
data validation trouble | Excel Programming | |||
Formula1 Property of Validation Object to refer to function name | Excel Programming | |||
Trouble with validation | Excel Programming |