Excel Protection and Allowed Users

[tessam]

Hello, I have a timesheet in Excel and have the formulas and certain areas locked and protected so that employees can't change them. I have areas unlocked so they can enter in their time. What I can't seem to do is allow certain users to edit locked ranges. I have set up the ranges and selected who I want to edit the ranges but when it is protected that person can't edit. Any suggestions?
Thanks Tessa

 

[longhair]

tessa,
how is it protected? if you can post some code / pseudo code i might be able to help. it may ba as simple as giving those 'certain' users the password for excel's protection function (if you are using the one provided by excel).
regards,
longhair

 

[tessam]

Longhair, It is protected thru excel's protection. I go to Allow users to Edit Ranges, select ranges,and password then permissions and add. I find the people I want and add them. When I protect the sheet they can't access the ranges I specified. Is there another way to pick the people instead of going to our network location. Or where can I store who I want access granted. thanks tessa

 

[DPlank]

How about a macro?

Something along the lines of

Private Sub Workbook_open()
userName = Environ("USERNAME")
if (userName = "PersonA") Or (userName = "PersonB") etc..then
Range("yourRange").locked = false
end if
End Sub

This way the workbook check on opening and if the user is specified unlocks the ranges for them.

Environ("USERNAME") uses the user's network login to for additional protection rather than Application.User, which can be easily edited.

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like