Trouble with Validation formula1:=Join(... Is '!' a reserved cha

[tbd]

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.

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

 

[Per Jessen]

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]

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!

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

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.

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

 

[tbd]

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!

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

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.

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