Share via

VB: Test if application is in a cut or copy state

Anonymous
2013-11-12T12:04:12+00:00

hi, i am trying to find a test for if excel currently has itself in a cut or copy state.  i don't know if the following is related, but is all can find.

it will basically be for another "PASTE" macro,  so it will not insert a bogus line if there is no currently cut/ copy "LINE" currently active, same as for accidently start of the Paste macro..   thanks.

i hit copy on a line, then tried to run the following, nothing happened.

    If Application.CutCopyMode = True Then

    MsgBox ("Macro Works")

    End If

==========    ANSWERS   (variations that work as listed below)

UPDATE:  have been working on this some.   will have to get back with mixing all previous eg's found on this top post, but i have some other answers that seem interesting.  these new items seem to show a NON STANDARD approach for what was made for the vb.  maybe something ms can fix:

(you can test any of these lines with the YES NO msgbox item below it.

'HHHMODE  HHHCUTCOPY

    'If Application.CutCopyMode <> xlCut And Application.CutCopyMode <> xlCopy Then   'yes

    'If application.CutCopyMode Then           'yes  test for either cutcopy mode to be true

    'If application.CutCopyMode = False Then   'yes

    'If Not application.CutCopyMode Then       'no   test for NOT either cutcopy mode

    'If application.CutCopyMode = True Then    'big friggin no, why

MsgBox "YES" & Space(15), vbQuestion

Else

MsgBox "NO" & Space(15), vbQuestion

End If

==========

The items below might work in simple message box,  but the example that got to work with other VB:

    'If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then    'both yes

---------- 

    If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then    'both yes

    'If Application.CutCopyMode = 0 Then         'yes:  0 off, 1 copy, 2 cut

    'If Application.CutCopyMode Then             'both yes cut-copy work / for all 3 states

    'If Not Application.CutCopyMode Then         'both yes

    'If Application.CutCopyMode = xlCut Then     'cut  yes

    'If Application.CutCopyMode = xlCopy Then    'copy yes

    'If Not Application.CutCopyMode = False Then 'yes for both on,  double neg skip

    'If Application.CutCopyMode = False Then     'yes for both off

    'If Application.CutCopyMode = True Then      'no for either on, does not work..

    MsgBox ("Macro Works")

    End If

  • or -

    If Application.CutCopyMode = xlCopy Then

        MsgBox ("Excel is Copying cells")

    ElseIf Application.CutCopyMode = xlCut Then

        MsgBox ("Excel is Cutting cells")

    End If

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2013-11-12T12:33:08+00:00

    Initiating a macro scrubs the .CutCopyMode to False just as it scrubs all Undo points. If you want to test for .CutCopyMode you must initiate a .Copy within the macro sequence of commands. Try,

    Sub aaa()

    ' .CutCopyMode will be false at this point

    Range("A1").Copy

    If Application.CutCopyMode Then

    MsgBox ("Macro Works")

    End If

    End Sub

    BTW, since CutCopyMode is already a boolean (True or False) it does not have to be compared to one in an If statement.

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2013-11-12T12:26:15+00:00

    Hi Dave,

    Try:

        If Application.CutCopyMode = xlCopy Then

            MsgBox ("Excel is Copying cells")

        ElseIf Application.CutCopyMode = xlCut Then

            MsgBox ("Excel is Cutting cells")

        End If

    Cheers

    Rich

    0 comments

9 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-11-12T13:06:24+00:00

    I have to disagree... in Excel 2007, 2010 and 2013 at least, running a macro does not kill the marching ants...

    Actually, it isn't the act of running the macro so much as opening the Macro dialog with Alt+F8. You're right in that there certainly are alternate methods of initiating a macro (a shortcut key come immediately to mind) and I should have been more specific. Thanks for catching that.

    Ah, I see.  Yes, I was running my code direct from the VBE, which I think Dave will be doing as it sounds like he is trying to debug his code(?).

    So now I see a very interesting "feature" of If statements in VBA.

    Assuming you manage to start the macro without killing the ants (using a shortcut key, command button or run from within the VBE), the line:

        If Application.CutCopyMode Then Msgbox "Excel is Cutting or Copying cells"

    will show the message, but:

        If Application.CutCopyMode = True Then Msgbox "Excel is Cutting or Copying cells"

    will NOT show the message! 

    To avoid confusion, I stand my previous recommendation to test for NOT FALSE.

    Cheers

    Rich

    0 comments
  2. Anonymous
    2013-11-12T13:02:04+00:00

    I have to disagree... in Excel 2007, 2010 and 2013 at least, running a macro does not kill the marching ants...

    Actually, it isn't the act of running the macro so much as opening the Macro dialog with Alt+F8. You're right in that there certainly are alternate methods of initiating a macro (a shortcut key come immediately to mind) and I should have been more specific. Thanks for catching that.

    0 comments
  3. Anonymous
    2013-11-12T12:51:38+00:00

    Hi Jeeped,

    I have to disagree... in Excel 2007, 2010 and 2013 at least, running a macro does not kill the marching ants.

    What I can see is that if you test

        ?xlCut=True

    or

        ?xlCopy=True

    they both evaluate to False, even when the ants are marching.

    But if you test

        If Not Application.CutCopyMode Then                         '!NO - see edit2 below!

            MsgBox "Excel is Copying or Cutting some cells"

        End If

    or you test the code I posted previously, you can see that Application.CutCopyMode does evaluate correctly.

    This is an issue with the way Excel/VBA compares True to xlCut and xlCopy.  True evaluates to -1 in VBA.  xlCopy = 1 and xlCut = 2, so you either need to directly test agains xlCut and xlCopy (as in my previous post) or test for Not False (i.e. not equal to zero) as in the above line in bold.

    Cheers

    Rich

    [EDIT: but given how easy it is to misinterpret the line "If Not Application.CutCopyMode Then", I would actually recommend comparing it to False in this case, even though, as you said, a comparison to a Boolean is not necessary in an If statement.  So I think this would be the best option:

    ****** If Not Application.CutCopyMode = False Then

            MsgBox "Excel is Copying or Cutting some cells"

        End If

    (or you could use <> instead of the 'Not' and the '=' )

    or use the direct test with xlCut and xlCopy in my first reply. ]

    [EDIT2: Also, just using If Not Application.CutCopyMode Then...does not work correctly if there are no marching ants!  See my reply lower down for a full list of acceptable ways to correctly test for marching ants...]

    0 comments