Solved

Protection of particular Elements in Excel

Posted on 2014-09-10
4
87 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 48

Accepted Solution

by:
Martin Liss earned 500 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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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