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

CodePudding user response：

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

1. Slow
2. Less Reliable, especially with pastespecial
3. 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