turn off protected sheet warning

Do you know how to turn off the dialog box that pops up when I try to modify a cell in a protected workbook.


Specifically:
I use some vba to protect and unprotect certain sheets in my workbook based on certain events. When a cell is double clicked my program
1)unprotects the worksheet
2)changes some cell contents based on the contents of the cell that was double clicked
3)turns protection back on

But when you double click a cell in a protected sheet this dialog box comes and it gets annoying. I tried
application.displayalerts = false but to no avail. Any clues???

example code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)

Unprotect
If ActiveCell = "yes" Then
Cells(1, 1) = "yes"
Else
Cells(1, 1) = "no"
End If
Protect

End Sub

Thanks

Hi,

Is it only 'certain' cells you need to double click? You can set the format of cells in a sheet so that they are not protected even if the sheet is.

What you can do is right click on a cell, select format/protection and then unclick the locked check box. This cell will then remain unprotected even when the rest of the sheet is. You should then be able to double click them without the message. If you have a value that you need to protect you may be able to change it back in the cell change event.

Hope this helps & apologies if you already knew this.

I played around with it for a bit and got everything to work. I need the cells to be protected at all times so that the only way for anything on the sheet to change is if you double click any cell. I added
cells(1,1).select

to the end of the sub that runs "before double click" and it seems to take care of the problem. Thanks for the input though.