VBA to remove Data Validation and then add it back

Hello,

This has taken me a while to get to work, but I have code, triggered by a button click, that will remove data validation on all sheets in a workbook. I needed this functionality to be able to use the workbook on a SharePoint 2010 page.
You can then re-enable the data validation by clicking the button again, and it adds the validation back to all the cells that had it before.

I hope this might help some people who are trying to remove data validation for one reason or the other. I found several sites for adding it to a cell and making it dynamic, but I couldn’t find ones that easily explained how to remove it (completely) from a cell. I ran the a macro for removing data validation and then copied the code, but that didn’t work for the SharePoint site, because when you did a Find for Data Validation, it still found all the cells that had it. Call me daft, but I final tried this and it worked. Just a simple Selection.Validation.Delete, without all the extra commands after it. i.e, .Add Type :=xlValidateInputOnly, ….

I am a little disturbed about how long it takes to re-enable the data validation on the cells though, and I’m wondering if setting up an array or some other similar process might help speed it up.

Anyway, perhaps this may help someone else out there, but any response for speed enhancements would be appreciated.

Thanks

hi mdewell, welcome to ExcelForum, please check attachment, press button (changed to Forms button instead of ActiveX) on Data sheet, the code has been amended

Hello watersev

Thanks for the new code. It does go a lot faster. I was trying to figure out how to get areas to work, but couldn't figure a way to do it. I am curious as to why you went Forms instead of ActiveX. Is there a reason to go with Forms versus ActiveX?

Thanks

If I remember correctly the changes were:

1. Removing all activate/select statements with apropriate referencing the object
2. Changing validation cell address recording from cells to areas
3. Changing the way the areas addresses outputed to the sheet
4. Adding Screenupdating on/off

Re button: There is no difference, I just like the way the form button looks. In order to hide all codes from macros menu except the main one all the "sub-subs" are private.