Application.DisplayAlerts = False does not disable alerts

[Derek Dowle]

The code ‘Application.DisplayAlerts = False’ does not work in all instances.

I have two virtually identical worksheets in different workbooks.

These worksheets act as an index and each cell is protected. By
double-clicking on a particular cell my VBA code gathers some data and
completes a procedure. The ‘Application.DisplayAlerts = False’ should
prevent the protection alert from appearing.

In the first workbook it works. The code was placed in ‘Private Sub
Worksheet_Activate()’. It did not work if it was placed at the beginning of
code in ‘Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
Cancel As Boolean)’.

Place the same code in the second workbook and it does not work.

I have also opened a new workbook. Sheet1 has been protected and the
‘Application.DisplayAlerts = False’ code placed in ‘Private Sub
Worksheet_Activate()’. If I DoubleClick the protect alert appears.

Is there a way around this problem?

The only thing that has changed since the first book was created is that
Vista Business Version 6.0.6002 Service Pack 2 Build 6002 has been installed.

Excel version used is Excel 2003.

 

[Ryan H]

It sounds like the default action for the double click event is firing. Its
trying to enter into a cell to edit it, but the sheet is protected so you get
the alert. Set Cancel = True in the BeforeDoubleClick event, like below.

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

Cancel = True
Application.DisplayAlerts = True

' rest of your code here

End Sub

Hope this helps! If so, let me know, click 'YES' below.

 

[Ryan H]

Or you may need unprotect the sheet at the beginning of your code so you
don't get any errors writing to the sheet and then reprotect the sheet at the
end of your code. Like so,

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

Cancel = True
Application.DisplayAlerts = True

ActiveSheet.Unprotect "password"

' rest of your code here

ActiveSheet.Protect "password"
Application.DisplayAlerts = False

End Sub

Hope this helps! If so, let me know. Click "YES" below.