Protection of particular Elements in Excel

Hi Experts,

I'm trying to unprotect then protect a worksheet in Excel.

The elements that I would like the users to be able to use are:

Select unlocked items
Edit Objects

The reason being that I have a form that opens when the user double clicks in a range and if I simply turn protection on they are unable to trigger the action to fire the open of the form (the double click).

One part of the code currently looks like this:

Dim myPassword As String
myPassword = "GHDORF"
Application.ScreenUpdating = False
If Range("OTHERACTIVITY").Value = True Then
'UnprotectSheet
Worksheets("ORF - Activities").Unprotect Password:=myPassword

I can't seem to work out how I set the property/elements as described above.

Any help would be awesome!

Thanks

Martywal
martywalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I'm not sure I understand what you want to do but did you select the 'Select unlocked items' and 'Edit Objects' options when you protected the sheet and prior to that did you go to Format Cels|Protection and deselect the 'Locked' checkbox for the range you want the user to be able to double-click?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martywalAuthor Commented:
Hi There.
The cells in the range are already locked.
The issue is that when I add protection to the sheet manually I can actually chose what elements I can protect on the worksheet.

When one of my scripts is run, in this case a double click of the mouse (within a certain range it fires a module that opens a form) the sheet is unprotected then protected at the end of running the module BUT not with the same elements available/unavailable to the user.

I'm now trying to replicate the issue and may have solved it... Can't reproduce the problem today. Very strange. I'll be back soon.

Thanks for comments!
0
martywalAuthor Commented:
Sorry not to have closed this sooner...
I have found that if I set the properties when I add protection to a sheet it holds the same as VB turns protection on and off.
Thanks for submissions
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.