double-click on cell formula in protected sheet activates sheet to which formula points

0
votes

Say I have a workbook with two worksheets in it.

I protect sheet2 via following code: Sub test() Worksheets(2).Cells.Locked = True Worksheets(2).Protect DrawingObjects:=False End sub

(I've set DrawingObjects:=False because I still want to be able to edit forms)

Protection does what I expect it to on sheet2 and when I double-click a cell it displays the warning "The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.".

However, if the double-clicked cell contains for example a SUM formula, it highlights the range to which the formula refers to. If it's a SUMIFS formula that refers to ranges in sheet1, it activates sheet1.

Any ideas on how this can be prevented without disabling double-clicking?

Thank you.

1
vbaexcel

1 Answers

1
votes

When protecting the worksheet, you just need to uncheck the option to

Allow all users of this worksheet to: Select locked cells

img

The VBA equivalent is:

ActiveSheet.EnableSelection = xlUnlockedCells

There are three options for enabling cell selection:

img

More Information:

Edit:

To allow users to select cells (and see the contents) but disallow double-clicking specifically, you could check the option for Select locked cells and then add code to the Worksheet Module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
End Sub

(To open the Worksheet Module, right click the worksheet's tab at the bottom of the screen and choose View Code.)

Note that users will still be able to edit the cell's formula in (and therefore outline the cell range included in the formula) by clicking in the formula bar or pressing F2 after selecting the cell (although the formula bar can be removed as well.)