CODEWITHSUNDEEP
excelvba
SincereApathy
SincereApathy

Reputation: 675

Excel: Disable alert about trying to change protected cell on BeforeDoubleClick

I have a worksheet that has a protected range of cells. These cells have names in them and I would like to have a before double click event that shows a form and pre-loads info in the form based on the name in the cell that is double clicked.

I'm using double click so the user gets the cell selected on the first click, then I can use ActiveCell.Value to get the name and load the appropriate information.

However, since I have protected the range, when I double click a cell I get an excel prompt saying

The cell or chart that you are trying to change is protected and therefore read-only.

To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.

The forms still triggers as planned, but I would like to stop the excel prompt from happening. Is there a way to disable the prompt? If not, is there another way of going about this that I'm not thinking of?

Upvotes: 2

Views: 17314

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

For your particular case this is happening because you have the workbook option enabled for 'Allow editing directly in cells' - which can be found here:

enter image description here

And can be set via code, e.g.:

Application.EditDirectlyInCell = True

So when you double click the event fires but the next action is to enter the cell to edit but because you have protected that cell, you get the alert. The simplest way to prevent the alert is to cancel the event before the 'edit' stage occurs - you just want to use the event to launch the form, right? So the event handler is like this:

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

    Dim varValue As Variant

    'get the cell value
    varValue = ActiveCell.Value

    'do something with it - you can launch your form here        
    MsgBox CStr(varValue)

    ' NOTICE here that you can cancel the double click event 
    ' meaning no attempt to edit the cell will be made per the users double click
    Cancel = True

End Sub

Upvotes: 9

Related Questions