Allowing Double Click to run code in protected sheet

codeliftsleep

Board Regular
Joined
Apr 14, 2017
Messages
103
I've got a dilemma I am faced with. I have a sheet that has GETPIVOTDATA formulas that I have created a BeforeDoubleClick Event that opens a new sheet containing the data reflected in the GETPIVOTDATA via VBA code.

It works without issues when I click the options to allow selection of locked cells when I protect a worksheet in Excel itself. You can't edit it but you can double click it just fine and the code works as it should. However, there is a part of the sheet that needs to update depending on the user who logs in, which means I need to unprotect the sheet, change the data and the re-protect the sheet. When I reprotect it in VBA, the double click ability goes away, saying the cell is locked even as I recorded the macro of me protecting it with the proper options selected in Excel.


Here is the macro it gave me:

Code:
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowUsingPivotTables:=True

When I used this code it still won't allow me to double click the cell as I could before when I did it by hand. How can I get this working properly?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top