?
Solved

Protection of particular Elements in Excel

Posted on 2014-09-10
4
Medium Priority
?
94 Views
Last Modified: 2014-09-29
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
0
Comment
Question by:martywal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40317457
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
 

Author Comment

by:martywal
ID: 40318208
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
 

Author Comment

by:martywal
ID: 40351553
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question