• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel removes all data validation lists at the opening of the workbook

A

akesy

New Member
Hi guys,

this thing is driving me crazy and i am getting desperate; here is the thing: I have developed a quotation model in Excel, with data validation lists, a simple VBA macro and when i think everything's working out, i get a message at the opening of the workbook stating that Excel found some unreadable content and that it must repair/recover the unreadable content; when i click OK, the workbook opens and a message appears stating that Excel successfully repaired/recovered the file by removing some feature: "Removed Feature: Data validation from /xl/worksheets/sheet6.xml part". When i open the log file repairs, here is the log:


<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error064840_02.xml</logFileName><summary>Errors were detected in file 'C:UsersAdmin7DocumentsCommercialLightingJobsQuotation - Park Regis - 18102012Quotation_ParkRegis_Hotel - MASTER.xlsx'</summary>-<removedFeatures summary="Following is a list of removed features:"><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet6.xml part</removedFeature></removedFeatures></recoveryLog>


Bottom line: All the data validation lists get removed from the file and my model no longer is usable.


I have been looking for a solution everywhere and i haven't been able to find any to my problem. I am not sure what's wrong with my model: I thought the original model was corrupted so i created the same model in another workbook but it happened again. I thought it was the VBA code that screwed everything up (I create a data validation list in one cell, from a string of dynamically generated list of characters, which i thought may get too long. So i created another workbook without the VBA code and the same problem happened again); sometimes you can create a few data validation lists and the problem doesn't appear, but it seems like if many lists are applied to many cells, then the problem appears.


Anyway, i have no idea of how to work it out and i hope one of you guys may be able to help me out with this; that'd be awesome, you'd be awesome haha


i can send the workbook if you provide me with an address.


MANY THANKS.
 
Hi, akesy!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Try to follow these steps:

a) if you have a backup, restore (or use) it and check if error persists

b) if yes, open the file and save it with another name, then add one data validation against a list, save it and check if error

c) if so, would you please upload your wrong file?, refer to second green sticky post at this forums main page for guidelines.


Regards!
 
Hi Sir,


thanks for replying, glad to be on board here as well, been following Chandoo's lessons and charting tips and it's been very rewarding for me and my employer as well haha.


That's why i was hoping you guys could take a look at my problem because i am sincerely running out of idea; i have been looking everywhere, in every possible forum, etc.. but haven't got a solution to my problem yet.


I have attached to this post a link to my file in dropbox as suggested ..


https://dl.dropbox.com/u/29163345/TestModelForCorruption.xlsm


If you try to open this file, you'll get the error message, which won't help you out much since if you click "YES", all the data validation lists will be erased. That's why i am attaching another file as well https://dl.dropbox.com/u/29163345/CL_BC_BASIC_UNCORRUPTED_VERSION.xlsm

This file is at a stage where excel doesn't trigger the error that erases all the data validation lists, so you can see where my lists are located (LU_DET_BA tab). You will notice the difference between the 2 files: in the first, there're more columns (and therefore lists) ... And this is what seems to get excel to trigger this error message: there's a point when you keep adding columns (still in tab LU_DET_BA) where excel will trigger the error and all the data validation lists will be erased.


Not sure if you'd be able to work it out but i'd greatly appreciate if someone could have a look at it.


Thanks A LOT.


Cheers,


Alex.
 
Hi Akesy and Sir JB,

I've got sort of the same problem; is it solved? maybe you could post the answer to this inquiry as well? Would very much be appreciated!!!

Kind regards,

Valery
 
Hi, Valery!

First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


There isn't an automatic and effective procedure to solve it, just trying the described steps.


Please follow the same prior indications given to akesy. Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Good day akesy


It is good practice to tell members when cross posting so that they do not waste their time and can see other results


http://www.excelforum.com/excel-general/870367-excel-removes-all-data-validation-lists-at-the-opening-of-the-workbook.html.


Are you running the work books on an earlier excel and you have data validation that is using 2007_2013 terms, ranges, ect,ect.
 
Hi Old friend


Sherlock...no..because of the lung problem the pleasures of smoking are no longer an option...so it you would like to but some new unused pipes you know where to come :)
 
You must log in or register to reply here.
Back
Top