Excel 2000 vs 2002 Sheet protection & Validation cells

[Andre Croteau]

Hello,

I have an Excel spreadsheet built on Excel 2000. I have set sheet
protection with password, and I have some unprotected cells with drop down
menu (validation with 50 choices) Everything works very well.

I sent this file to a friend who has Excel 2002. The same "unprotected"
validation cells cannot be modified in his version as the message says that
these cells are protected

What can be done in order to make it work on his version?? I do have some
"very hidden" sheet with password protected VBA properties, if that is an
issue.

Similarly there are some macros on that same spreadsheet that work on Excel
2000 with the sheet protection on, yet it doesn't work in Excel 2002 with
sheet protection on.
Any comments would be appreciated.

Thanks in advance.

André

 

[Jim Rech]

All these things should work fine. I just created a workbook with a
validation dropdown and sheet protection on in Excel 2000 and it worked fine
in Excel 2002. I think I'd start over again making sure the file I sent was
okay. You can send it to me if you want and I'd be happy to test it. Clear
out anything propriary.

 

[Hari]

Hi Jim,

I came across something opposite.

I created a macro in which if data in a cell is inconsistent then that cell
would get shaded with yellow pattern.

The code I wrote in 2002.

Sheets(sheetName).Cells(n, a).Interior.ColorIndex = 6

The above used to run fine in 2002

But when the same sheet/code was run in 2000 then I used to get some sort of
run time error and the code stopped executing exactly at the line of code
pasted above.

When the same computer's excel was upgraded to Excel 2003 then the code ran
fine.

Regards,
Hari
India

 

[Andre Croteau]

Hello All,

I did send my file to Jim, and he noticed that my "Unprotected" cells were
in fact "locked"
I assumed that they were unprotected since I was able to modify the content
of the cells with a drop down validation.

As Jim pointed out:
A good question is why the drop downs work in Excel 2000 and not 2002 (and
2003 btw). I think the answer is that MS re-worked worksheet protection in
Excel 2002 and "fixed" this intentionally or unintentionally.


Thanks Jim for all your help!

André

 

[Jim Rech]

Sheets(sheetName).Cells(n, a).Interior.ColorIndex = 6

I guess I'd have to know the exact circumstances the error occurred in and
the full error message to figure out what the problem is. Of course the code
above runs fine in 2000 if you just stick it in a new workbook and run it.