Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Which Range is currently being Cut or Copied ?

  1. #11
    Board Regular
    Join Date
    Nov 2009
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Which Range is currently being Cut or Copied ?


  2. #12
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Which Range is currently being Cut or Copied ?

    Quote Originally Posted by ISY View Post
    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #13
    Board Regular
    Join Date
    Mar 2016
    Posts
    222
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Which Range is currently being Cut or Copied ?

    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:
    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:
    Code:
    Public strNewRange As String
    Public strMarchingAntsRange As String
    Public CutCopyActivated As Boolean
    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  4. #14
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Which Range is currently being Cut or Copied ?

    Quote Originally Posted by shknbk2 View Post
    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.

    Last edited by Jaafar Tribak; Sep 21st, 2019 at 12:28 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #15
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Which Range is currently being Cut or Copied ?

    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:
    Code:
    Option Explicit
    
    Private WithEvents cmndbrs As CommandBars
    
    #If  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
    #Else 
        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
    #End  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:
    Code:
    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •