BeforeDoubleClick Event on Protected Sheet

Hello.

I have a protected sheet that calls a UserForm in the Worksheet_BeforeDoubleClick event. However, when I am all said and done with the UserForm and reactivate the sheet, it gives me the password protect message (since I activated the form with a double-click on a protected cell). Is there a way to disable this? I tried:

Please Login or Register  to view this content.

...as the first code in the Worksheet_BeforeDoubleClick event, but that did not work. Is this doable, or will I need to use another event and/or object to call the UserForm?

Thanks in advance for any help.

Jason

Hi Jason,

You'll have to unprotect the sheet before opening the form. To do this, replace YourPassword in the following with the relevant password for the sheet...

Please Login or Register  to view this content.

...then open the form.

To re-protect the sheet, end the macro with the next bit of code (again replacing YourPassword with the relevant password for the sheet):

Please Login or Register  to view this content.

Cheers,

Robert

Hi Robert. Thanks for the timely reply.

I don't think it is a timing issue with the UserForm. I have data entered from the UserForm into the sheet, but I do in fact unprotect the sheet, enter the data, then reprotect the sheet. The problem is that since Excel is recognizing that the user is double-clicking on a protected cell, once the VBA is finished, and the sheet has the focus again, Excel prompts that the sheet is protected.

I have no issues getting the data from my UserForm onto the spreadsheet.

Does that explanation make sense?

By the way, based on your reply, I tried unprotecting the sheet first (within the BeforeDoubleClick event) prior to opening the UserForm, and the result is the same.

Thanks,

Jason

Hi Jason,

You'll have to stop the default practice for the double-click event i.e. editing the active cell, prior to doing anything else. To do this, I used the following SendKeys method (even though I don't like it, I couldn't think of another way) as my first line of code:

Please Login or Register  to view this content.

The other alternative is to use a command button as you won't be editing anything by clicking it.

Hope this helps,

Robert