Problem with List Validation

[Cameron MacNeil]

Hi there. I'm having a weird problem when trying to create a list of
values in a spreadsheet. The list worked fine before, when only 5
values were in the list, but now, when I want to add 2 more, it gives
me a "Method 'Add' in object 'Validation' failed" error. Could anyone
tell me why?

This is the list of values I need, and below is the line that is
getting an error. Thanks!

reasonList = "Company No Longer In Business/L'enterprise a fermé ses
portes," & _
"Investigations Action/Mesure d'enquête," & _
"Not In Region/Hors région," & _
"On-Site Review < 2 Years/Examen sur place < 2 ans," & _
"Company Has Restructured/L'entreprise a restructuré," & _
"Other Approved Reasons/Autres raisons approuvées," & _
"Broker or Courier/Courtier ou Messager"

xls.Cells(row, P1colNOTSELECTEDREASON).Validation.Add xlValidateList,
xlValidAlertStop, xlBetween, reasonList

 

[DM Unseen]

seems to me a limit of 255 characters on the property that holds the
formula.

You need to use a range as your list instead


DM Unseen

 

[Cameron MacNeil]

Thanks! One more question...how do I do that?

 

[Dave Peterson]

Put those values into a worksheet (hidden?) and give it a nice name.

Then record a macro when you add data|validation to a test cell.

Take a look at Debra Dalgleish's site to see how to use the range name:
http://www.contextures.com/xlDataVal01.html