How to prevent double click event working on a protected cell

**danbates** · 07-29-2017, 02:28 AM

Hi,

I was wondering if someone could help me please?

I have a double click sub that works fine but I would like it not to work on a protected cell.

I've tried looking it up but the only answers I've found is by using a cancel button on a message box. I would prefer not to use a message box.

Is there another way to do this?

Here's my code.

Please Login or Register  to view this content.

Kind Regards
Dan

**6StringJazzer** · 07-29-2017, 03:18 AM

Your sub will not even be invoked if the user double-clicks on a locked cell when the sheet is protected, so I'm unclear on what you're looking for.

First let's get terminology lined up: A cell can be locked or unlocked. A worksheet can be protected. If a sheet is protected, you can select options to restrict what a user can do to locked cells on that sheet. If a cell on a protected sheet is unlocked, then there are no restrictions on that cell.

What is it you want to happen if the user double-clicks a locked cell on a protected sheet? Excel will display a dialog box telling the user that the action is not allowed, even if you have a Worksheet_BeforeDoubleClick sub.

By the way, your assignment to the cell probably works but is misleading. You may want to use

Please Login or Register  to view this content.

**danbates** · 07-29-2017, 04:21 AM

Hi Jeff,

Thank you for replying.

To start with the cell is unlocked on a protected sheet. I double click in that range and it puts the time in. After I've done a few other things on my sheet I enter my password and it protects certain cells.

When I double click the cell again after its been protected it comes up with "Run time error 1004" Application defined or object error, and when I debug it it highlights

Please Login or Register  to view this content.

I would like the protected double click cell to do nothing if its double clicked by mistake when protected.

I put your code in but it comes up with a syntax error

Thanks again

Dan

**6StringJazzer** · 07-29-2017, 09:14 AM

There should not be a "]" at the end of the line, that was a typo. I removed it in my post.

Please attach your file. I am still not getting your description. You first said the cell is unlocked on a protected sheet. Then you say you "enter [your] password to protect certain cells," which I can't understand. You don't need to enter a password to lock cells, and if the sheet is already protected I can't figure out how you would be entering a password.

**danbates** · 07-29-2017, 09:59 AM

Hi Jeff,

Sorry I'm not describing this very well to you.

I have 14 columns on my worksheet and I only need to enter into 9 of them. So 5 are always locked and protected.

Once a shift (Day and Nights) I enter details into the 9 columns and on the last column I enter into which is column L, a userform triggers and asks for my initials and password which then locks the 14 columns. Thinking about it I could change the code to do just the unlocked cells but it works.

What I'm trying to say is column C is one of the columns that is unlocked and not protected at the start of the shift. Once I fill out the other cells in that row, I save my file via the userform and then that locks the 14 cells.

When I then double click on cell C again after its been locked and protected, that's when it comes up with the 1004 run time error.

I appreciated your help on this Jeff

Kind Regards
Dan