+ Reply to Thread
Results 1 to 4 of 4

Data Validation error after running macro. Is my "list" too long?

  1. #1
    Registered User
    Join Date
    06-20-2018
    Location
    Los Angeles, California
    MS-Off Ver
    MS Office 365
    Posts
    1

    Data Validation error after running macro. Is my "list" too long?

    I am getting the error below whenever I try to re-open a file that was created using vba script. I F8 through the code step by step and the error happens after a 3rd Data validation sequence. The first two have identical coding, run fine, and if I save the file after each point and then reopen the file, it opens fine (and shows the dropdowns that were created). If I continue stepping through, the file no longer reopens after the 3rd identical sequence (script is exactly the same). The only difference in the code is the number of items in the validation list. The first two lists have 2 and 9 items in the "list". The 3rd has 18 items in the list. I thought about using reference cells to create the list instead of writing the text into the code, but there would still be 18 objects referenced. Does that make a difference? If I save the file after letting it continue the macro and stopping after that 3rd sequence (they are all back-to-back), the file seems fine because it finishes "open". The dropdowns work perfectly. But if I save and close the file, then try to re-open it, I get the error below. Here is the code and the error. Any help is appreciated.

    Error-CC.JPG

    With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="1211 InventoryNPC,1400 Construction in Progress,6271 Employee Benefits,7020 Advertising,7044 Supplies kitchen,7053 Office supplies,7054 Printing cost,7055 Postage/Mailing/Fed Ex,7056 Parking,7057 R&D expense,7061 Outside services,7063 Recruiting cost,7066 Auto expense,7068 Consultants,7070 Repairs and maintenance,7076 Sales Commission Expense,7116 Cell Phone,7127 Meals and entertainment,Miscellaneous"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    Selection.ClearContents
    Selection.Copy
    Range("C5:C100", Range("C5:C100").End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Data Validation error after running macro. Is my "list" too long?

    It is probably that your string is too long (404 characters). It is odd, but VBA has a different string max length than Excel.
    https://stackoverflow.com/questions/...a-vba-function
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Data Validation error after running macro. Is my "list" too long?

    The limit is for Data Validation, not VBA. Although you can assign a string longer than 255 characters, it will fail file validation when the file is reopened. It is much better to use a range somewhere to hold the values - and easier to maintain too!

  4. #4
    Registered User
    Join Date
    07-24-2018
    Location
    USA
    MS-Off Ver
    Microsoft Office 2016
    Posts
    5

    Re: Data Validation error after running macro. Is my "list" too long?

    It is very helpful. thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Long Macro Running too Fast actually Pastes "#GETTING_DATA"
    By phelbin in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-12-2019, 05:00 AM
  2. Macro to "Jump to" or find data based on a data validation drop down list
    By dropkickmurphy08 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-05-2016, 02:19 AM
  3. [SOLVED] Data validation "list" - "source" has too many characters
    By bee88 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-21-2015, 04:28 PM
  4. List Data Validation + option of "INC"+12 digit number"
    By penfolda in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2013, 02:53 AM
  5. Macro code too long: "Procedure too large" Error
    By nachousa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2010, 03:16 PM
  6. [SOLVED] Data Validation: Allow "List" or "Whole number"
    By monir in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2006, 11:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1