ExcelBanter - View Single Post - Trouble with Validation formula1:=Join(... Is '!' a reserved cha

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!

"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