How do I return the location of the marching ants in Excel?

Question

I know about Application.CutCopyMode, but that only returns the state of the CutCopyMode (False, xlCopy, or xlCut).

How do I return the address of the currently copied range in Excel using VBA? I don't need the currently selected range (which is Application.Selection.Address). I need the address of the range of cells with the moving border (marching ants) around it.

In other words, if you select a range of cells, hit CTRL+C, and then move the selection to another cell, I need the address of the cells that were selected when the user hit CTRL+C.

Thanks!

Answer 1

As far as I know you can't do that with vba. You can however code your own copy sub and store the source in a global variable.

Something like this:

Option Explicit
Dim myClipboard As Range

Public Sub toClipboard(Optional source As Range = Nothing)
    If source Is Nothing Then Set source = Selection
    source.Copy
    Set myClipboard = source
End Sub

Answer 2

When you copy a Range, the address is copied to the Clipboard along with other formats. You can check that with Clipboard Viewer application. So if you need the copied Range, get it from Clipboard. It will be something like> $A2:$B5 or similar

Answer 3

The only way i can think of doing this is tracking the last range selected with a global variable and then waiting until you think a copy action is done. Unfortunately neither is easy.

The following is a quick attempt that has two problems;

1. If you copy the same data twice it isn't updated
2. If a copy or paste is fired from another app, the results may vary.

This is one of those last hope tricks when tracking events that don't really exist. Hope this helps.

''# Add a reference to : FM20.dll or Microsoft Forms 2.0
''# Some more details at http://www.cpearson.com/excel/Clipboard.aspx

Option Explicit

Dim pSelSheet As String
Dim pSelRange As String

Dim gCopySheet As String
Dim gCopyRange As String

Dim gCount As Long
Dim prevCBText As String

Dim DataObj As New MSForms.DataObject



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
        ByVal Target As Excel.Range)

    CopyTest
    pSelSheet = Sh.Name
    pSelRange = Target.Address


    ''# This is only so you can see it working
    gCount = gCount + 1
    application.StatusBar = gCopySheet & ":" & gCopyRange & ", Count: " & gCount
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    CopyTest ''# You may need to call CopyTest from other events as well.

    ''# This is only so you can see it working
    gCount = gCount + 1
    application.StatusBar = gCopySheet & ":" & gCopyRange & ", Count: " & gCount
End Sub




Sub CopyTest()
    Dim curCBText As String
    Dim r As Range
    DataObj.GetFromClipboard

    On Error GoTo NoCBData
      curCBText = DataObj.GetText
    On Error Resume Next


    ''# Really need to test the current cells values
    ''# and compare as well. If identical may have to
    ''# update the gCopyRange etc.

    If curCBText <> prevCBText Then
      gCopySheet = pSelSheet
      gCopyRange = pSelRange
      prevCBText = curCBText
    End If

  Exit Sub


NoCBData:
  gCopySheet = ""
  gCopyRange = ""
  prevCBText = ""
End Sub

Oh and excuse the wierd comments ''# they're just there to help the syntax highlighter of SO.