Solved

Protection of particular Elements in Excel

Posted on 2014-09-10
4
77 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
  • 2
4 Comments
 
LVL 46

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now