Sleeper: Allow users to edit range - problem

[splashscreen]

Hi, striclty not a vba question (yet) , but I have an odd problem with password protecting an excel sheet.

I have a pass protected sheet with a couple of ranges that can be unlocked with a (different) password for each range. However some pairs of ranges are unlocked by inputting the right pass for only 1 of those ranges.

ex.

Range Password
A             12
B             34
C             56
D             78

I have a situation similar to this: range A is unlocked with pass '12' , but instead of the pass only unlocking range A, it also unlocks range C and vica versa with the pass for C.

How is this possible? It seems like a bug to me. Anyone heard about similar problems?

Thanks for any info on this.

[Ken Puls]

Hi Splashscreen, and welcome to VBAX!

I'm intrigued by your question... exactly how are you protecting/unprotecting specific ranges with different passwords? In my experience, (and I learn something new every day,) worksheet protection is just that... worksheet level. I've never known a way to protect different ranges on a sheet with different passwords.

[splashscreen]

Hi , Thanx

Check:

In Microsoft Excel 2002 and later, you can now use passwords to protect specific ranges in your worksheets. This is a change from earlier versions of Microsoft Excel, in which one password applies to the entire worksheet, which might have several protected ranges. In addition, if you use Microsoft Windows 2000, you can apply group-level passwords and user-level passwords to different ranges.

[Ken Puls]

Heya,

Very cool, thanks! Somehow I never saw that menu option. Strange the things that you don't notice when a new version is released...

I set up everything using the exact passwords that you have, and couldn't replicate your issue. Are you sure that the passwords are different, and that you haven't selected both ranges when you set up your ranges in the protection screens?

[splashscreen]

I'm certain that I selected only the intended ranges. I'm fairly certain that all the passwords are unique (generated a string of random numbers for each pass). Though I can't check atm because the actual file itself is in my account at work.

[Ken Puls]

Hmm...

Attached is a copy of what I put together to test. Used your passwords for the ranges (they're coloured). Password to unprotect the sheet is blank (no characters).

[Greg T]

I too cannot duplicate the problem. And this was also new to me. Them pre-shrunk menus are not always such a good deal - I'd never even seen that menu option. Maybe time to switch that option off...

[Ken Puls]

Them pre-shrunk menus are not always such a good deal - I'd never even seen that menu option. Maybe time to switch that option off...

LOL! Unfortunately, I can't use that excuse. That was the very first thing I figured out how to stop in 2000. Why anyone thought it was a good idea is beyond me. Maybe for someone who only uses 20 commands, but if you really use a program to half of it's potential? I found it maddening!

[splashscreen]

Ok, thx. I'm gonna have another look at the file again tomorrow. I'll keep you uptodate

[splashscreen]

Right. I have checked the original file and there are no double ranges defined and all passwords are unique.

I've played around with it a bit and found that if you change the pass of one of the conjoined ranges it does stop unlocking the other range, but switches to just unlocking another row..

But,
I have found a workaround for the situation and that is to define passwords that only contain letters. This way there are no mutually unlocking ranges present in the locked sheet.