Which Range is currently being Cut or Copied ?

[Jaafar Tribak]

Hi dear forum members,

I have a vba situation where I need to know the address of the range that is currently being copied (or cut) ie: when Application.CutCopyMode <> 0 .

I wonder if I am overlooking some easy solution or missing something obvious.

Regards.

 

[ISY]

Hi

Alternative

https://www.pcreview.co.uk/threads/referring-to-a-range-that-has-been-copied-excel-vba.3947067/

 

[Jaafar Tribak]

Hi

Alternative

https://www.pcreview.co.uk/threads/referring-to-a-range-that-has-been-copied-excel-vba.3947067/

Hi ISY,

No . That doesn't work ... It does get the prevoiusly selected range but not the range that has been copied or cut.

Regards.

 

[shknbk2]

Jaafar - your code is by far the best solution. I've already created a .xlsm file to store it.

However, merely as a curiosity for the code in the previous link, I thought I'd try to improve that code a little to see if it could work somewhat. I think this might work, but it is limited. In order to clear out the previously-saved marching ants location, the CutCopyMode must be False, and a new range selection must be made before it can register a new Cut/Copy range.

Scenario: Make a selection change while not in CutCopyMode. Copy/Cut the range. Select a new range. This will then store the copied range address that can be used if needed. The copied range address does not get cleared when another new selection is made while in xlCut or xlCopy. Only after clearing the CutCopyMode and making a new range selection will the copied range address be available again. Here's the problem: if a new range is selected during xlCut/xlCopy and the new range is cut/copied (to change the marching ants to the new selection), the copied range address will not be cleared and will still refer to the previous xlCut/xlCopy range. If this limitation can be lived with (which I likely wouldn't), this code could be useful.

Worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then
        If Not CutCopyActivated Then
            CutCopyActivated = True
            strMarchingAntsRange = strNewRange
        End If
        strNewRange = Target.Address
        Debug.Print strMarchingAntsRange
    Else
        CutCopyActivated = False
        strNewRange = Target.Address
        strMarchingAntsRange = ""
    End If
End Sub

Code to put in Module:

Public strNewRange As String
Public strMarchingAntsRange As String
Public CutCopyActivated As Boolean

 

[Jaafar Tribak]

Here's the problem: if a new range is selected during xlCut/xlCopy and the new range is cut/copied (to change the marching ants to the new selection), the copied range address will not be cleared and will still refer to the previous xlCut/xlCopy range. If this limitation can be lived with (which I likely wouldn't), this code could be useful.

In my initial attempt to solve this, I resorted to a similar code but as you found out, it doesn't always give the correct range.

Thanks shknbk2.

 

[Jaafar Tribak]

The code I posted in post#9 works well with contigious and non-contigious ranges but after some more testing, I found that if you copy a non-contigious range and select another worksheet or select a worksheet in a diifferent workbook then the GetCutCopyRange function errors out or simply returns the incorrect range... It turned out that storing the range address inside the Tag Property of a commandbar control can be problematic when navigating between sheets\workbooks...Also, the GetClipboardSequenceNumber API erroneous values when moving between worksheets\workbooks.

So I have decided to re-write the code so that it also works accross worksheets and workbooks.

Workbook example


1- In the ThisWorkbook Module:

Option Explicit

Private WithEvents cmndbrs As CommandBars

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetClipboardSequenceNumber Lib "user32" () As Long
    Private Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
    Private Declare PtrSafe Function SetWindowText Lib "user32" Alias "SetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String) As Long
    Private Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetClipboardSequenceNumber Lib "user32" () As Long
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Private Declare Function SetWindowText Lib "user32" Alias "SetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String) As Long
    Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call SetCommandBarsHook
End Sub


Private Sub SetCommandBarsHook()
    
    Dim sWBkName As String, sSheetName As String, sRangeAddr As String
    
    If cmndbrs Is Nothing Then
        With Application
            Call GetFullAddress(sWBkName, sSheetName, sRangeAddr)
            Call SetWindowText _
            (GetDesktopWindow, Trim(sWBkName & "|" & sSheetName & "|" & sRangeAddr & "|" & GetClipboardSequenceNumber))
            Set cmndbrs = .CommandBars
        End With
    End If

End Sub


Private Sub CmndBrs_OnUpdate()

    Dim sCutOrCopiedRangeAddr() As String
    Dim sWBkName As String, sSheetName As String, sRangeAddr As String
    
    With Application
        If TypeName(.Selection) = "Range" Then
            If Len(GetDesktopWndText) Then
                sCutOrCopiedRangeAddr = Split(GetDesktopWndText, "|")
                If sCutOrCopiedRangeAddr(3) <> GetClipboardSequenceNumber And .CutCopyMode <> 0 Then
                    Call GetFullAddress(sWBkName, sSheetName, sRangeAddr)
                    Call SetWindowText(GetDesktopWindow, sWBkName & "|" & sSheetName & "|" & _
                    sRangeAddr & "|" & GetClipboardSequenceNumber)
                    sCutOrCopiedRangeAddr = Split(GetDesktopWndText, "|")
                End If
            End If
        End If
    End With

End Sub


Private Sub GetFullAddress(ByRef x As String, ByRef y As String, ByRef z As String)

    Dim sTemp As String
    
    On Error Resume Next
    
    sTemp = Application.ActiveWindow.RangeSelection.Address(False, False, , True)
    x = Left(sTemp, InStr(sTemp, "]") - 1)
    x = Replace(x, "[", "")
    If Left(x, 1) = "'" Then x = Right(x, Len(x) - 1)
    z = Right(sTemp, Len(sTemp) - InStrRev(sTemp, "!"))
    y = Replace(Right(sTemp, Len(sTemp) - InStr(sTemp, "]")), z, "")
    y = Left(y, Len(y) - 1)
    If Right(y, 1) = "'" Then y = Left(y, Len(y) - 1) 

End Sub


Public Function GetDesktopWndText() As String

    Dim lRet As Long, sBuff As String * 256
    
    lRet = GetWindowText(GetDesktopWindow, sBuff, 256)
    GetDesktopWndText = Left(sBuff, 256)

End Function

Code Usage:
2- In a Standard Module:

Option Explicit

Sub Test()

    Dim oCutCopyRange As Range
    Dim sCutOrCopyOperation As String

    Set oCutCopyRange = GetCutCopyRange

    If Not oCutCopyRange Is Nothing Then
        sCutOrCopyOperation = IIf(Application.CutCopyMode = xlCopy, "Copied", "Cut")
        sCutOrCopyOperation = "Range being *" & sCutOrCopyOperation & "*" & Chr(32) & ":" & vbNewLine & vbNewLine
        MsgBox sCutOrCopyOperation & oCutCopyRange.Address(False, False, , True)
    Else
        MsgBox "No range being cut or copied !", vbCritical
    End If

End Sub


Function GetCutCopyRange() As Range

    Dim sCutOrCopiedRangeAddr() As String
    Dim oSheet As Worksheet
    
    If Application.CutCopyMode <> 0 Then
        sCutOrCopiedRangeAddr = Split(ThisWorkbook.GetDesktopWndText, "|")
        Set oSheet = CallByName(Workbooks(sCutOrCopiedRangeAddr(0)).Sheets, "Item", VbGet, sCutOrCopiedRangeAddr(1))
        Set GetCutCopyRange = CallByName(oSheet, "Range", VbGet, sCutOrCopiedRangeAddr(2))
    End If
    
End Function

Now that the code works accross diff worksheets and workbooks, ideally, one would add the Macro in the personal workbook to get the current cut\copy range and could attach it to a button in the Quick Access Toolbar for easy global use throughout the application.

 

[hamiltpj]

Ok- Finally, I seem to have managed to get through this by detecting the change in the clipboard serial number within the commandbars OnUpdate event and so far, the code seems very robust.

The GetCutCopyRange function now works with contigious as well as non-contigious ranges


Workbook example


1- Code in the ThisWorkbook Module:

Option Explicit

Private WithEvents CmndBrs As CommandBars

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetClipboardSequenceNumber Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL]
    Private Declare Function GetClipboardSequenceNumber Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private Sub Workbook_Activate()
    Call SetCommandBarsHook
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call SetCommandBarsHook
End Sub

Private Sub SetCommandBarsHook()

    If CmndBrs Is Nothing Then
        With Application
            .CutCopyMode = 0
            .CommandBars.FindControl(ID:=128).Tag = _
            .ActiveWindow.RangeSelection.Address(False, False, , True) & "|" & GetClipboardSequenceNumber
            Set CmndBrs = .CommandBars
        End With
    End If

End Sub


Private Sub CmndBrs_OnUpdate()

    Dim sCutOrCopiedRangeAddr() As String
   
    With Application
        If TypeName(.Selection) = "Range" Then
            sCutOrCopiedRangeAddr = Split(.CommandBars.FindControl(ID:=128).Tag, "|")
            If sCutOrCopiedRangeAddr(1) <> GetClipboardSequenceNumber And .CutCopyMode <> 0 Then
                .CommandBars.FindControl(ID:=128).Tag = _
                .ActiveWindow.RangeSelection.Address(False, False, , True) & "|" & GetClipboardSequenceNumber
                sCutOrCopiedRangeAddr = Split(.CommandBars.FindControl(ID:=128).Tag, "|")
            End If
        End If
    End With

End Sub

Usage Example
2- Code in a Standard Module:

Option Explicit

Sub Test()

    Dim oCutCopyRange As Range
    Dim sCutOrCopyOperation As String

    Set oCutCopyRange = GetCutCopyRange

    If Not oCutCopyRange Is Nothing Then
        sCutOrCopyOperation = IIf(Application.CutCopyMode = xlCopy, "Copied", "Cut")
        sCutOrCopyOperation = "Range being *" & sCutOrCopyOperation & "*" & Chr(32) & ":" & vbNewLine & vbNewLine
        MsgBox sCutOrCopyOperation & oCutCopyRange.Address(False, False, , True)
    Else
        MsgBox "No range being cut or copied !", vbCritical
    End If

End Sub



Function GetCutCopyRange() As Range

    Dim sCutOrCopiedRangeAddr() As String
   
    If Application.CutCopyMode <> 0 Then
        sCutOrCopiedRangeAddr = Split(Application.CommandBars.FindControl(ID:=128).Tag, "|")
        Set GetCutCopyRange = Range(sCutOrCopiedRangeAddr(0))
    End If

End Function

This works great following a user copy/cut command but returns the wrong range after a programmatic range.cut or range.copy command.

Any ideas?

 

[Jaafar Tribak]

This works great following a user copy/cut command but returns the wrong range after a programmatic range.cut or range.copy command.

Any ideas?

I am afraid, I don't know how to get the range that has been cut\copied programmatically. I refer to non-contiguous ranges.

 

[Rick Rothstein]

This works great following a user copy/cut command but returns the wrong range after a programmatic range.cut or range.copy command.

Any ideas?

I may be missing something (probably am), but if you are issuing the Cut or Copy method within a VBA procedure, don't you already know the range you are about to perform this operation on?

 

[Jaafar Tribak]

I may be missing something (probably am), but if you are issuing the Cut or Copy method within a VBA procedure, don't you already know the range you are about to perform this operation on?

You are actually correct Rick... I was thinking more like strictly speaking. Maybe the OP should clarify.

Thanks.

 

[hamiltpj]

I may be missing something (probably am), but if you are issuing the Cut or Copy method within a VBA procedure, don't you already know the range you are about to perform this operation on?

In the project I'm working on, my worksheet_change code causes the marching ants to disappear, so the user can't cut/copy/paste anymore. What I therefore want to do is capture the cut/copy area at the beginning of the handler and then reinstate it at the end.