Applying a doubleclick macro to a cell with data validation

[jeffreybrown]

Can you help me understand what I'm missing here? In D6 I have data validation with numbers. When I double click on D6 I want the word "Works" added to cell H6. All this works fine with the macro below.

As soon as I password protect the sheet I can't get the double click macro to run. When double clicking D6 I just get the spinning wheel and no action. If I hit escape the spinning wheel stops, but still no action.

If I remove the data validation from D6 the code runs fine even protected. I feel I've missed something obvious, but I can't see it.

Please Login or Register  to view this content.

[CK76]

It's the issue with how Data Validation in-cell dropdown works on protected sheet.
When sheet is protected, as soon as you select the cell holding in-cell dropdown, dropdown will display and interrupts the code.

Basically it stops code from proceeding until data validation dropdown is confirmed / cancelled.
And when you confirm the action... that supersedes BeforeDoubleClick() event, and that event is no longer triggered.

You can get around this by allowing DrawingObjects.

Please Login or Register  to view this content.

[jeffreybrown]

Thank you so much CK76. This is great.

[CK76]

You are welcome and thanks for the rep