Programmatic validation client disconnect error

In relation to this thread on mrexcel:

http://www.mrexcel.com/board2/viewto...09022&start=20

I am getting this error:

Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.

The code that always (apparently) causes the error is this specific stanza:

Code:

---

Case "ABC"
          With Selection.validation
              .Delete
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
 xlBetween, Formula1:="ABC_ blah, ABC_ bleh bleh bleh blah, ABC_ hmm hmm, ABC_ ack, ABC_ ugh, ABC_ ich ick ack, heck_ huck, ann_24 snimmen snop, him_Hum Heck, Harl_Hill Hup, Ipsum_Lorum, cat_fish, cat_fish bicycle, woe_is Me, who_Knows Jack, JIm_Bob JOhn, Bim_Bam Boom, Bada_Bing's Thing, doo_wah Ditty, apple_Pear Square, Line_dot Spot, Upsy_Daisy Mazey Crazy, Where_Is_Waldo?"
              .IgnoreBlank = True
              .InCellDropdown = True
              .InputTitle = ""
              .ErrorTitle = ""
              .InputMessage = ""
              .ErrorMessage = ""
              .ShowInput = True
              .ShowError = True
          End With

---

The nonsense is substituted for confidential data, but all the punctuation therein is real.

Any ideas?

Excel is the only office application I have open, and my code is not meant to interact with any others (if that makes a difference in terms of this type of error).

Thanks...

miconian,

After a bit of a fiddle, I find that the error does not occur until the nonsense below exceeds 256 characters --
"ABC_ blah, ABC_ bleh bleh bleh blah, ABC_ hmm hmm, ABC_ ack, ABC_ ugh, ABC_ ich ick ack, heck_ huck, ann_24 snimmen snop, him_Hum Heck, Harl_Hill Hup, Ipsum_Lorum, cat_fish, cat_fish bicycle, woe_is Me, who_Knows Jack, JIm_Bob JOhn, Bim_Bam Boom, Bada_Bing's Thing, doo_wah Ditty, apple_Pear Square, Line_dot Spot, Upsy_Daisy Mazey Crazy, Where_Is_Waldo?"

Maybe this is an inbuilt limitation. I suggest that if you want to use a list of valid choices that you instead create a named list of the choices and refer the data validation to the list. This should remove the 256 character limitation.
If you need to keep the list hidden form view, the named list approach will allow you to place the list on a separate, hidden, password protected sheet.

As in
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Some_list"

Kieran,

I actually had already started using a named range as an alternative, but I am glad to hear that there is a 256 char limit...it "makes sense," and is at least a rule that I can file away and know not to break.

Thanks.