I've been asking tons of questions lately about problems I'm having updating a workbook. I put out a patch program yesterday, and it caused more problems than it fixed! Here's the scenario:
There's about 35 of us keeping track of our reports in workbooks (XL2007, Vista). There's maybe 250 of these trackers in various server locations. Many are used as shared files.
The boss decided we needed a new tracker and asked me to build it. I created a workbook that met the requirements: named ranges with lists for validation drop-downs, columns A-Q locked, a macro to insert and delete rows in the locked area, 12 different conditional formats, and some other bells and whistles. Saved it as XLSM and put it on the server so it can be downloaded as needed and filled with information, then szved as a new file.
Now it's a few months down the road, and it's time to fix the glitches we've found. I pounded out a program using Word and Excel VBA, wrote up (what I thought were) detailed instructons, and sent it out. I think it took 10 minutes for my phone to start ringing!!
I'm limited in how and what I can do for a patch program:
-- I'm not a trained programmer, just a VBA fanatic
-- can't access the VBE to change code
-- gotta do everything in VBA or VBscript
This patch ran Excel through Word, and included a "new and improved" XLSM file with updated code modules. I copied all the data sheets into the new file, which put their data on top of new code. Then I fixed formulas and updated lists and such.
And of course there were problems! Yes, some due to bad code on my part. But many more due to the various ways 35 people managed to mangle a file so my code wouldn't run!
Of course I now think that had I created an actual template file, some of this would have been avoided. An add-in would have helped too. With 250 data-filled workbooks out there, is it too late to migrate to this?