[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1298
  • Last Modified:

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

0
RogueStat
Asked:
RogueStat
  • 11
  • 5
1 Solution
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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
 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now