protected sheet...two password posible?one has ability to change only specific range?

[PaSha]

hy people...

i was wondering... is it posible althought i think no, but asking is not a big problme...

can i make it so that i protect my sheet twice...and hyve two passwords...if one password is printed then the user (admin) would have all the acces... but when a second password would be printed then that user would have access only for a specific range to edit??

[PaSha]

i've searched the web and found some info
but i don't know if this is correct ... actually it doesn't work...

i don't know why or how...

a idea was to make code: Worksheets("blabla").Range("a2:a15").AllowEdit = True

and the second one was Range("a2:a15").Locked = False




hope some one can help me pick this code together to work lol

[grichey]

that code is only locking or unlocking cells which affects them when the sheet is protected. You need to understand the difference in protected and locked. If the sheet is unprotected, locked vs unlocked means nothing. If the sheet is protected, locked cells can only be edited or selected by the allowed means; whereas unlocked are still treated as completely normal cells.

That said, in order to have more than one password, you'd have to set up a table that had a master password and your 'sub passwords'. When your input box reads in the 'sub', it would then have to pass the master to to the protection function to unprotect, then re-protect with the options you want the user to have unlocked. When you entered it again, it would have to do the opposite, passing in the master, and removing the options for the user and relocking the entire sheet.


There may be an easier way to do this, but if so, it eludes me.

[Bob Phillips]

You could redirect the Protect and Unprotect controls to your macro and in this check the username and throw up a login userform depenedent on the user.

[grichey]

yeah - wouldn't you still have to have a table of some sort to control which user had which permissions?

[unmarkedhelicopter]

you could do it with hidden names if you really distrust your users but I don't think PaSha is quite upto this level of coding unless you are going to do it for him.

[grichey]

..wish I had the time... I am glad he asked though. I'll probably try to come up with something portable to handle this after year end...

good luck pasha in the meantime!

[rory]

If you are on a domain, you can assign specific permissions to specific users for specific ranges, using Tools->Protection->Allow users to edit ranges, if that helps.

[PaSha]

hey guys... thank you all for the replay...

i'm glad you answered you pointed me again in the right way...

somehow i managed to solve this problem...

what i have done is this ...

i selected the range i was planing to give acces to change and said they are now Unlocked...

then i redirected the protect sheet to the vba like xld said...

in the sheet general code i wrote:

[VBA]
Worksheets("name").Protect Password:="master"
Worksheets("name").Protect Password:="sub"
[/VBA]

then i made two little command buttons... one for unlocking and one for locking the sheet again

in the unlocking i made this :

[VBA]

Dim UserName As String
UserName = InputBox("Write your password: ")

If UserName = "master" Then Worksheets("name").Unprotect password:="master"
If UserName = "sub" Then Worksheets("name").EnableSelection = xlUnlockedCells

End Sub

[/VBA]

and in the lock button

[VBA]

Private Sub lockButton_Click()

Worksheets("name").EnableSelection = xlNoSelection
Worksheets("name").Protect Password:="master"

End Sub

[/VBA]

and it works good!!

so thanks again to all ... if someone knows a bettere or more elegent way to write this please post... i would like to see it...

greathings...

[unmarkedhelicopter]

I must be thick ... I don't see how that could possibly work ...

[PaSha]

I must be thick ... I don't see how that could possibly work ...

hehe - i know it looks funny - but it works ... i traied it for several times till then and it works...

i can explain what was my idea and what i have done...

i selected the ranges which i was thinking to give acces to change for specific user... i marked it Unlocked... just that range...all other ranges are locked...
then i password protected the sheet and disselected the options : Select locked Cells and Select unlocked cells, which is by default enabled when you protect the sheet... but i disabled also that so nothing on the sheet can be clicked, just the buttons...

then i made those two buttons like i said ... and opened the vba code and wrote that an inputbox comes and when the user inputs his password and this password is correct i wrote : Sheets.EnableSelection = xlUnlockedSelection which in this case allows the user the select the range which i unlocked it ... but everything else is still protected...
and then he can change those ranges ... and when he clicks the other button to lock the sheet again ...i wrote : EnableSelection = xlNoSelection
so i disabled that which was enabled with the first button and then password protected the sheet again...


i was just improvising... and somehow it works ...

[rory]

This code:
[VBA]Worksheets("name").Protect Password:="master"
Worksheets("name").Protect Password:="sub"
[/VBA]
can just be:
[VBA]Worksheets("name").Protect Password:="master"
[/VBA]
since the second line should not change the password.

[PaSha]

oh yeah... you're right rory ...
my mistake ...