Strange Error: Excel found unreadable content in...

Hey guys, I have the following code snippet in excel's On Open.  

When the code runs, evrything up to this point works fine.  When I execute the code in vba, the data validation populates correctly and everything works fine.  However, when I save and reopen excel, the file errors out and gives me this message:

Excel found unreadbale content in 'Filename' Do you want to recover th ecntents of this workbook?  

I say yes, the file opens, and the validaiton is gone.  Have I used the wrong syntax or something?
With Sheets("Data").Range("B2").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
          xlBetween, Formula1:=StDatesForVal
     .IgnoreBlank = True
     .InCellDropdown = True
     .InputTitle = ""
     .ErrorTitle = ""
     .InputMessage = ""
     .ErrorMessage = ""
     .ShowInput = True
     .ShowError = True
 End With

Open in new window

LVL 1
RogueStatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RogueStatAuthor Commented:
FYI, the sheet worked correctly all day yesterday and just started this behavior this morning.
0
RogueStatAuthor Commented:
Also, I've tried moving things to new workbooks and everything.  The problem is definitely isolated to that section of code.
0
Rory ArchibaldCommented:
What is StDatesForVal?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

RogueStatAuthor Commented:
It's just a string of unique values.  In the code above it loops through a list of dates and makes a string with each unique member.  It looks like this:

"Day1, Day2, Day3, etc."

If I run the code immediately, it populates the box correctly.  Also, to be sure it wasn't the string, I tried populating the box with just "test" and it worked in immediate but then the same error occured  after saving and reopening.

What bothers me is that other parts of the On Open work correctly.  There is a part that sets a name for a range, for example.  That works fine, just this one part messes up.
0
Rory ArchibaldCommented:
Could you post the worksheet, minus any sensitive data?
0
RogueStatAuthor Commented:
Sure, I will make a sample that replicates the validation and error.  Thanks
0
RogueStatAuthor Commented:
This is weird, the error is not repeating when I strip out some things.  Let me look at this more and get back to you, sorry.   I don't want to waste your time if it's something I've just overlooked somehow.
0
RogueStatAuthor Commented:
Okay, scratch that... it has something to do with the string of dates.  I changed it to a different string of numbers and it worked.

The string is simply "1/1/2010,1/2/2010, etc."

I'll see if I can isolate the problem.
0
RogueStatAuthor Commented:
Okay, the issue is the lenth of the string.  Originally the string had 32 dates, but I tried running the loop fewer times and it generated 25 dates.  It worked.  I tested it and it errored out after 28 dates.  

The problem can't be the raw length of the string, since it's not that long.  

Also, I did a similar procedure and replaced the dates with numeric values (1 through 32 for example) and it worked fine, so the issue isn't with the number of items in the data validation drop down.  

Strange, no?
0
Rory ArchibaldCommented:
If you use a formula string, you are limited to 255 characters.
0
RogueStatAuthor Commented:
Okay one last comment.

I tried to upload the .xlsx test file and it wasn't on the extension list.  So I saved the file as .xls and the error does not occur!  

Here is the xls file.  If you save as and make it a macro-enabled 2007 file it should occur.
ErrorTest.xls
0
Rory ArchibaldCommented:
Your DV list is 396 characters. That is too long!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RogueStatAuthor Commented:
I see, I was confusing the amount a string could hold with what excel could evaluate.  

For what I'm trying to do, should I output the array of unique dates to a hidden worksheet and refer to that range?  I wanted to avoid putting the dates anywhere but the string approach obviously doesn't work.
0
RogueStatAuthor Commented:
The string value cannot be evaluated beyond 256 characters.  My error occured after adding enough elements that the code captured too many characters.
0
Rory ArchibaldCommented:
That would be my choice, yes, although when you get to a certain number of entries in a DV list, I think a userform becomes preferable.
0
RogueStatAuthor Commented:
Sounds good, thanks for the help  I didn't understand why it suddenly blew up, but that makes sense now.  I appreciate it!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.