How to disable warning on doubleclick

[Gringoire]

Dear Gentlemen,
I'm using the doubleclick event to trig a procedure on a protected Sheet. I would like to avoid the warning message that appears when the user double click on a Cell.
I used this code but it does not work.
Could you please show mw where I'm wrong?

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
With Application
    .DisplayAlerts = False
    .AlertBeforeOverwriting = False
    .ScreenUpdating = False
End With
...
'here is my procedure
...
With Application
    .DisplayAlerts = True
    .AlertBeforeOverwriting = True
    .ScreenUpdating = True
End With
End Sub

thanks.

 

[Michael M]

What is the warning message ?
If it is a warning about a protected sheet...you will have to incorprate the sheet UnProtect in your code then Reprotect at the end of the code !

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
With Application
    .DisplayAlerts = False
    .AlertBeforeOverwriting = False
    .ScreenUpdating = False
End With
 Worksheets("Sheet1").Unprotect
'here is my procedure
Worksheets("Sheet1").Protect..
With Application
    .DisplayAlerts = True
    .AlertBeforeOverwriting = True
    .ScreenUpdating = True
End With
End Sub

 

[Gringoire]

[EDIT]: If this could help, I noticed that the warning appears after the End Sub instruction is executed.

 

[Gringoire]

What is the warning message ?
If it is a warning about a protected sheet...

Yes, it is.

you will have to incorprate the sheet UnProtect in your code then Reprotect at the end of the code !

Yes I did it just now, but the message still appears.
It seems like the warning message is triggered after the BeforeDoubleClick event is completed...

 

[Michael M]

You could try using in the workbook open event

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect password:="PASSWORD", UserInterfaceOnly:=True
End Sub

The see if the double click event works

 

[Gringoire]

Thanks for your help Michael, but I already implemented such a procedure in workbook_open() from the beginning of my project.
this is the Sub called by workbook_open()

Sub Protezione(Cartella As Worksheet, stato As Boolean)
'=============================
' Protegge / sprotegge un foglio Excel
'=============================
Dim PWD As String
Dim dummy As Long

PWD = "xxx"
If stato = True Then                            'Proteggo il foglio
Cartella.Protect Password:=PWD, _
    DrawingObjects:=True, _
    Contents:=True, _
    Scenarios:=True, _
    UserInterfaceOnly:=True, _            '<=======
    AllowFormattingCells:=False, _
    AllowFormattingColumns:=False, _
    AllowFormattingRows:=False, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=False, _
    AllowSorting:=False, _
    AllowFiltering:=False, _
    AllowUsingPivotTables:=False
ElseIf stato = False Then                       'Sproteggo il foglio
    Cartella.Unprotect (PWD)
Else
    dummy = MsgBox("Errore protezione foglio")
End If
End Sub

 

[Gringoire]

For information: I solved this issue adding the following instruction just before the End Sub statement, when myprocedure is already completed.

Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
...
'myprocedure
...
If target.Locked Then Cancel = True
End Sub