Excel 2003 / Excel 2010 Compatibility Issue ??

[monirg]

Hello;

My earlier Thread in Excel Questions Forum:
VBA: Opening XL 2003 w/b in XL 2010 Produces Error ‘1004’ ??
http://www.mrexcel.com/forum/showthread.php?t=583852

I’d like to regard the problem as a compatibility issue rather than an excel question; particularly for those who might be interested in the subject or have encountered a similar problem.

Here’s in a nutshell a description of the problem and my earlier solution.

1) I need to transfer many of my XL 2003 workbooks (developed and run perfectly on different computers, XL 2003, Win XP 32-bit) to my new laptop (Win 7 Pro 64-bit, Office 2010 full version, 32-bit).

2) Any of those XL 2003 workbooks (with w/s password protection, macros, w/s events) produces in XL 2010 the error:
“Run-time error ‘1004”

3) In XL 2010, and been annoyed with the run-time error, I tried a w/b Open() event to unprotect the w/s in the transferred XL 2003 w/b, and a w/b BeforeClose() event to protect the w/s before exiting. Also tried calling a Sub to unprotect the w/s directly before doing anything. Nothing worked.
The Run-time error ‘1004’ persisted and appeared at different locations / different routines depending on the code changes.

4) Even unprotecting the w/s in the XL 2003 w/b before transferring the file to the new m/c running XL 2010 and then protecting the w/s in the XL 2010 environment before save/open didn’t work!
My earlier “inconvenient” solution (post # 6 in the above link)

to manually remove the w/s password protection (either before or after the transfer), and keep the w/s unprotected

remains the only solution (so far!) that’s working for me in XL 2010.

5) Since MS claims that a working XL 2003 w/b would run successfully AS IS in XL 2010 (i.e.; no VBA code modifications, no added events, no w/s attribute changes), I wonder if the problem should be looked at by MS Excel Development Team to possibly identify the XL 2010 bug and fix the problem once and for all.
(Will post here the MS solution, if any!)

Any comments ?? Is communicating with MS Excel Team worth the time and effort ??

Regards.

 

[monirg]

Hi xenou;

xenou wrote:
there are quite a lot of ways you can protect your data. you have probably chosen the weakest one of them all - password protecting a worksheet.

That is absolutely fascinating and very interesting to explore! Not just for me but probably for many excel users!

xenou wrote:
I wouldn't mind hearing more about why you have chosen this method (w/s password protection)

Well, primarily to prevent me later from unintentionally changing blocked cells (data and/or formulas), and in some instances, I simply don’t want to reveal critical formulas.
I know for fact that w/s password protection doesn’t necessarily provide a high level protection, but it provides some nevertheless.
Let me put it this way. It's a habit, and it is hard to break old habits!

I feel that we’re drifting slowly but surely from the topic here, and I wonder if I should start a new Thread in Excel Questions Forum, specifically addressing the data protection issue.
A Thread with a Title like:
w/s Protection: Are There Ways of Protecting Data in XL 2010 Other Than with a Password ??
It would be a hot subject, I think, if it hasn’t already been discussed at length.

Regards.

 

[xenou]

I can understand the reasoning, sort of (I do the same in one case where I think I might update the workbook in the wrong place because of a hack that would tempt me if I wasn't thinking). But I generally find worksheet protection and workbook protection (with passwords) more trouble than its worth. I might do this in one or two workbooks, but not 50 of them. Mostly because they make writing vba more troublesome, and also I don't like having to constantly protect/unprotect sheets when I'm using them. I previously used read-only protection a fair bit, with workbooks that I only updated once a month. I do actually protect charts, but just so that mouseclicks won't move them around or show chart handles.

My main strategies are: Keep backups of all important workbooks. Use clearly defined structures so it's obvious where changes are appropriate or not appropriate. Separate input data from process data and output data. Using input cells and input data ranges. Never leave confidential information in any workbook going to anyone who should not have it (no matter how safely I think I have protected or hidden it). Use operating system permissions to protect files rather than passwords. I create check figures and validations to confirm results are correct and alert me when expected conditions have been violated. Aside from that, if I just want to make my workbooks modestly protected, I use invisible formatting, very hidden sheets, or send out washed copies that remove the process data and only retain the result data. Most of my "public" workbooks are copies of the real things that stay safe and sound. I am fortunate that I don't have to use any shared workbooks and can generally keep track of my own work in Excel, and also I can employ some Access tools and data import/export strategies so that I can work with others' data without giving them access to my workbooks. (I guess that works both ways, I've had one boss that was terrified I would "do something" to his cash flow worksheet, which was a huge mass of manually typed-in data. Actually, I would surely have turned it upside down if I had a chance - so he basically wouldn't let me near it which was fine by me!) In fact, most of my workbooks have some default "security through obscurity" built in - which is that most people can't figure them out (meaning, they can't figure out where to type or paste the numbers in, not realizing there are no numbers to type or paste in and I'm pulling the data in automatically and running it all by formulas).

I consider that many Excel users can't unhide a sheet or find white-colored text, while then the savvy user won't be phased even by passwords or very hidden sheets. So I aim to turn aside the casual prying eyes only, which works for non-critical purposes, and for critical purposes I assume the worst - that I have to assume my users can crack passwords and find hidden sheets and so on.

 

[monirg]

Hi xenou;

Thank you kindly for sharing your strategies. Very helpful.

Regards.

 

[xenou]

I should add that I am *not* up to speed on the latest changes in password security with the newest versions of Excel - so I may be out of date to some extent so far as that goes.

ξ