Prevent a MsgBox from a different VBA script from popping up

Question

I have written a script in the workbook that sends a msgbox warning users not to copy and paste cells whenever they do so.

Target.PasteSpecial xlPasteValues
    If Application.CutCopyMode = xlCopy Then
        MsgBox ("Please do not copy and paste cells. This can cause errors in log sheet")
    ElseIf Application.CutCopyMode = xlCut Then
        MsgBox ("Please do not copy and paste cells. This can cause errors in log sheet")
    End If
    
    Application.CutCopyMode = False

The problem is I have made other scripts assigned to buttons which are used to copy and paste specific cells when the function is called, but I get my own warning message pop up when this happens. Is there a way to prevent the msgbox popping up in these instances?

I have tried;

Application.DisplayAlerts = False

Application.DisplayAlerts = True

But this doesn't seem to work with scripted msgboxes

Why don't you share the complete procedure (from `Sub` to `End Sub`) and its location (worksheet name) and add more detail describing why and when this is happening, what exactly the procedures are doing,... etc. — VBasic2008, Jan 27 '23 at 01:27

score -1 · Accepted Answer · answered Jan 27 '23 at 00:28

Don't use copy/Paste method for many reasons.

Slow
Less Reliable, especially with pastespecial
Is causing you issues in this case.

Try instead to make values or formulas = source values or formulas.

Example:
Copy/Paste:

Sub Copy_Paste()
    
    Dim srcRG As Range  'Source Range
    Dim dstRG As Range  'Destination Range
    
    Set srcRG = Sheet1.Range("B2:B6")
    Set dstRG = Sheet1.Range("C2:C6")
    
    'Copy all
    srcRG.Copy dstRG
    
    'Or Copy Values
    srcRG.Copy
    dstRG.PasteSpecial xlPasteValues
    
    'Or Copy formulas
    srcRG.Copy
    dstRG.PasteSpecial xlPasteFormulas
    
End Sub

Becomes:

Sub Values_Formulas()
    
    Dim srcRG As Range  'Source Range
    Dim dstRG As Range  'Destination Range
    
    Set srcRG = Sheet1.Range("B2:B6")
    Set dstRG = Sheet1.Range("C2:C6")
    
    'Copy values
    dstRG.Value = srcRG.Value
        
    'Or Copy formulas
    dstRG.Formula = srcRG.Formula
    
    'Or Copy cell color
    dstRG.Interior.Color = srcRG.Interior.Color
    
End Sub

Won't throw error.

At the Bottom of this link you can find a list of properties you can copy this way

Prevent a MsgBox from a different VBA script from popping up

1 Answers1