DisplayAlerts and CutCopyMode

[ButtFace]

Hey All, Happy Friday-

After a bit of googling I haven't found a way to force DisplayAlerts to stay False. According to the quote below, Excel's behavior automatically resets it to true whenever code stops executing.
"If you set this property to False, Excel sets this property to True when the code is finished, unless you are running cross-process code."

In a nutshell, I'm trying to suppress the Name Exists Dialog when the user copies and pastes data between copies of the same Template. The Template has many locally-scoped names, and when the user copies formulas between worksheets, they get the standard "Would you like to use the Destination version of the name..." message (or something to that effect). I've tried to suppress the dialog via DisplayAlerts by checking CutCopyMode on Worksheet Activation (e.g., Application.DisplayAlerts = IIf(Application.CutCopyMode, False, True)), but as soon as the procedure quits then DisplayAlerts resets to True (per above), and when user attempts to paste data they are in turn presented with the very dialog I'm trying to suppress.

I've been looking for a workaround to force DisplayAlerts to stay false until CutCopyMode = 0, but have yet to find anything. One interesting note is that it stays False if "you are running cross-process code.", but I have no idea what that means or how to implement it.

Short of intercepting keystrokes and CommandBars, I'm unsure of how to proceed, and know of no way to intercept the right-click context menu. Might just be something they'll have to live with...

Does anyone here know of a workaround for this?

Thanks!

 

[6StringJazzer]

Deleted, I just realized this won't work.

 

[ButtFace]

Deleted, I just realized this won't work.

Thanks for giving it a shot!

 

[6StringJazzer]

I think the only way to control this is to provide a mechanism in code for the user to copy and paste. That will require a little design work and probably protecting sheets. Hard to be more specific without knowing what the file looks like or what the users are allowed to do.

 

[Jaafar Tribak]

Hi ButtFace

Give this a shot and see if it works for you :

Code to be placed in the ThisWorkbook Module:

Option Explicit

Private WithEvents CmmbTrue As CommandBars
Private WithEvents CmmbFalse As CommandBars
Private bStatus As Boolean


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    EnableDisplayAlerts = Not CBool(Application.CutCopyMode)
End Sub


Private Property Get EnableDisplayAlerts() As Boolean
    EnableDisplayAlerts = bStatus
End Property

Private Property Let EnableDisplayAlerts(ByVal Status As Boolean)
    bStatus = SetDisplayAlertsStatus(Status)
End Property

Private Function SetDisplayAlertsStatus(ByVal Status As Boolean) As Boolean
    If Status Then
        Set CmmbFalse = Nothing
        Set CmmbTrue = Application.CommandBars
    Else
        Set CmmbTrue = Nothing
        Set CmmbFalse = Application.CommandBars
    End If
   
    SetDisplayAlertsStatus = Status
End Function

Private Sub CmmbFalse_OnUpdate()
    If Application.DisplayAlerts Then Application.DisplayAlerts = False
End Sub

Private Sub CmmbTrue_OnUpdate()
    If Not Application.DisplayAlerts Then Application.DisplayAlerts = True
End Sub