Cell protection--causes problem with macro

[Snowgirl]

Hello,

I tried to look for this issue in other threads on this forum, but could not find this issue.

I have an Excel spreadsheet in 2003 that has a single column with a Vlookup that I need to protect. When I do this, this causes a problem with the macro I have on the sheet. It does not work and a box comes up with an option to debug. I need to protect this formula, but need the macro to work as well.

This is the macro I have on the sheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Change this address to the area where you want the click to produce a checkmark
Const sCheckAddress As String = "ab3:ab1500"
Dim rngIntersect As Range

On Error Resume Next
Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target)
On Error GoTo 0

If Not (rngIntersect Is Nothing) Then
Target.Font.Name = "Marlett"
Target.Value = "a"
End If


End Sub


Thanks for any help you can give me!!

 

[xenou]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Change this address to the area where you want the click to produce a checkmark
Const sCheckAddress As String = "ab3:ab1500"
Dim rngIntersect As Range

Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target)

[COLOR="Blue"]Me.Unprotect "password"[/COLOR]
If Not (rngIntersect Is Nothing) Then
    Target.Font.Name = "Marlett"
    Target.Value = "a"
End If
[COLOR="Blue"]Me.Protect "password"[/COLOR]

End Sub

This leaves your password visible in the code module. You may as well password protect that too (under Tools | Options in the code editor).

 

[Snowgirl]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Change this address to the area where you want the click to produce a checkmark
Const sCheckAddress As String = "ab3:ab1500"
Dim rngIntersect As Range
 
Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target)
 
[COLOR=blue]Me.Unprotect "password"[/COLOR]
If Not (rngIntersect Is Nothing) Then
    Target.Font.Name = "Marlett"
    Target.Value = "a"
End If
[COLOR=blue]Me.Protect "password"[/COLOR]
 
End Sub

This leaves your password visible in the code module. You may as well password protect that too (under Tools | Options in the code editor).

Hi Xenou! Thanks for your help!

I tried this and the macro does work without the error message! The unusual thing is that the protection on the sheet does not seem to hold. I protect the sheet and then try to delete one of the protected cells and it allows me to . Then I check and the menu says protect sheet again.

I did not protect the code module like you recommended--do you think that would help? And if so, could you tell me where I would find the option to password protect in the code module? I see Tool | Options, but I don't know where to go from there. (Please forgive my lack of knowledge on this)

Thanks again!

 

[xenou]

Hmm...
That's odd. Protection should hold. I find protection troublesome on the whole and try to live without it as much as possible. The theory being that those who delete cells they shoudn't get what they deserve.

I would just step back and watch the behaviors. what is the state before my macro? what is the state after my macro? Have I locked the cells? (This is relevant - protection is useless if cells aren't locked). Be spock, in other words. Logical. Computers - they just do what you tell them to do. Not very bright.

ξ

(its a little late for logical thinking, here...but I'll stay with you if you can't find a solution. Should be a piece of cake, I guess. In the morning, that is. When I'm awake... )

 

[Snowgirl]

Hello,

Thanks for your advice xenou! I agree about protection--it can be quite troublesome.

This is what is happening with the protection turning off. I have my macro set with the code you recommended for the protection. The macro is to double check in certain cells and a check mark appears. With this in place, if I try to delete a cell in my vlookup, I get the box that I cannot because the cell is protected. Good.

Then I try my macro--double click for the check mark--it works! The check mark appears. Good.

Then I try to delete one of the vlookup cells and I can. I check the protection and it says protect sheet. Protection is now off.

I am also having issues on this sheet with autofilters--I need them to work and I have tried code to enact the autofilters--I can get them to appear on the spreadsheet, but they do not work.

I appreciate any further help you can give me!!

Thanks,
Erika

 

[xenou]

1) What file format are you using (xls? xlsm?)

2) If this file is a shared file you may have much more trouble as shared files have limitations and restrictions. Protecting a sheet is probably one of them.

3) You may need to set the protection parameters - in your code you can allow filtering, disallow deletes, disallow formatting changes, and so on. Read the help file on protect.

If you are doing this in a shared file I'm not sure you'll be able to do all the same things you can in a regular file.

 

[Snowgirl]

Thanks again xenou!

The format is .xls and the file is not shared

I will keep experimenting.

Thanks!

 

[xenou]

Does the column where you are putting the check marks need to be protected anyway? You might want to check what cells are locked/unlocked. Unlock everything then lock the vlookup formulas and other regions that need to be protected.

It's very odd that you can't protect your worksheet in code though. I've never heard of this. Maybe it's some odd thing about trying to protect a cell during an event which is also changing its contents.

Can you just leave this particular column unprotected? Don't you need the cell to be unprotected anyway so you can click in it to make the checkmark? Or could you even just type the "a" in there and not use code at all ... ?

 

[Snowgirl]

Hi Xenou,

The column with the macro check mark does not need to be protected. The column with the Vlookup is the only thing I need protected. The issue was that when I protected the vlookup column--it made the macro in the check mark column not work.

I am not clear how to protect the worksheet in code--I will have to try that--would you happen to have any instructions for this?

I need the double click check mark to work because once this file is ready, I will be turning it over to many users and it needs to be easy to use.

Thanks again!

 

[xenou]

Okay. It seems you must set the protection to allow formatting. I forgot this. Is that all there is to it? I created a test workbook. As a test of concept it works for me. Obviously your book is more complicated; however, I did not need to protect/unprotect and was still able to run the double-click code.
Here's the sample file: <a href="http://northernocean.net/etc/mrexcel/20110227_protect.zip">Test Book (zip)</a>
(MD5 hash for the zip file: 55978b57007db55f1f1bd3e48d0ff8fb)
Note: The sample file was written to a) keep a lookup range protected in column A, and b) run double click code in another column on the worksheet. I also tried not to have to protect/unprotect in the code.

By the way, if you set the protection in code you should be able to use the "UserInterfaceOnly" option which should allow you to make changes in code without needing to unprotect the sheet. This isn't something I've used a lot but my limited experience with it has been positive so far.

Another note - your users might need to "uncheck" too ... will that be possible? If they make a mistake and check something by mistake?


This is how I set up the protection rules:

<img alt="protection dialog" src="http://northernocean.net/etc/mrexcel/20110227_protect1.png" />
<img alt="protection dialog" src="http://northernocean.net/etc/mrexcel/20110227_protect2.png" />