none
Copy and paste/Cut and paste is not working properly in excel macro RRS feed

  • Question

  • Hi,

    I have a scenario in macro where copy and paste/cut and paste should not work.

    When I copy/cut the data from A1 cell and trying to paste in B1 cell it should not get pasted and it should throw an validation message. I'm using the below macro code, but it is not working properly.

    If Application.CutCopyMode = xlCopy Then
        Application.EnableEvents = False
           For Each Cell In Target
            If Not (Cell.Validation.Value) Then
                Application.Undo
                counter = counter + 1
                cellAddress = Cell.Address
                Application.CutCopyMode = False
                Exit For
            End If
        Next
        If counter <= 0 Then
         Application.Undo
         If oldSelecedVal = Target.Column Then
            Target.PasteSpecial Paste:=xlPasteValues
         End If
        End If
        Application.CutCopyMode = False
    Else Application.CutCopyMode = xlCut Then
        Application.EnableEvents = False
        Application.Undo
        Application.CutCopyMode = False
                Exit For
            End If
        Next
    End If
     If counter >= 1 Then
            MsgBox "Invalid Value Pasted. Rolling back paste activity."
        End If


    Sathish Tallam

    Tuesday, February 13, 2018 12:24 PM

All replies

  • Hello Sathish,

    Did you try to debug the code? 


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Tuesday, February 13, 2018 5:29 PM
  • Hi Sathish Tallam,

    (1) Your code has many syntax error.

    you need to write 'elseif' instead of just 'else'.

    (2) You use 'Exit For' without for loop.

    (3) You use 'Next' without 'For'.

    (4) You use 'EndIF' without using 'IF' condition.

    So I suggest you to correct the above errors.

    You had mentioned that,"When I copy/cut the data from A1 cell and trying to paste in B1 cell it should not get pasted and it should throw an validation message"

    For that you can try to refer example below.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = xlCopy Then
      MsgBox "Invalid Value Pasted. Rolling back paste activity."
     Application.Undo
     ElseIf Application.CutCopyMode = xlCut Then
      MsgBox "Invalid Value Pasted. Rolling back paste activity."
     Application.Undo
     End If
    End Sub

    If this is not what you want then you can try to provide your requirement in detail.

    We will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, February 14, 2018 6:09 AM
    Moderator
  • Hi Deepk,

    Here the corrected macro code.

    If Application.CutCopyMode = xlCopy Then
        Application.EnableEvents = False
           For Each Cell In Target
            If Not (Cell.Validation.Value) Then
                Application.Undo
                counter = counter + 1
                cellAddress = Cell.Address
                Application.CutCopyMode = False
                Exit For
            End If
        Next
        If counter <= 0 Then
         Application.Undo
         If oldSelecedVal = Target.Column Then
            Target.PasteSpecial Paste:=xlPasteValues
         End If
        End If
        Application.CutCopyMode = False
    ElseIf Application.CutCopyMode = xlCut Then
        Application.EnableEvents = False
        Application.Undo
        Application.CutCopyMode = False
    End If

     If counter >= 1 Then
            MsgBox "Invalid Value Pasted. Rolling back paste activity."
        End If

    I have tried the below code which you provided already and it didn't worked for me. The problem is the value of Application.CutCopyMode is returning always 0 when I'm performing either cut or copy and xlCopy value is 1,xlCut value is 2 therefore the if-else condition is getting failed every time.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = xlCopy Then
      MsgBox "Invalid Value Pasted. Rolling back paste activity."
     Application.Undo
     ElseIf Application.CutCopyMode = xlCut Then
      MsgBox "Invalid Value Pasted. Rolling back paste activity."
     Application.Undo
     End If
    End Sub


    Sathish Tallam

    Wednesday, February 14, 2018 1:27 PM
  • Yes, But I didn't the expected output.

    Sathish Tallam

    Wednesday, February 14, 2018 1:36 PM