Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a range that has been copied (Excel-VBA)
Hi,
Can anyone confirm if there is a way in VBA to refer to a range that has just been copied but that it is not the selected range? For example: I select Range A1:A2, I press Ctrl+C and dotted moving lines appear around A1:A2. Now, I move to any other cell, say B4. At this stage, A1:A2 remain highlighted with the dotted lines, so I understand that the Application "knows" of them, therefore how can I return their address through code? Selection.address will obviously not work as it is no longer the selected range...I looked within the Application object but could not find anything... The fact that Excel knows that the range A1:A2 is the range that will be copied upon a paste operation, makes me think that the range is "active" in memory in some way and it could be retrirvable information, but perhaps it is one of those things the object model hides from us.. I tried reading the clipboard, but there you only get the values of those ranges, not the ranges as Range objects, with address property, formula property, etc... Any ideas? Many thanks Leo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a range that has been copied (Excel-VBA)
I'm not sure if there is a way to access the range properties from the
clipboard. This code will display the range address of the range you are copying or cutting. Hope this helps! If so, let me know, click "YES" below. Put this code in the Worksheets SelectionChange Event: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' get previously selected range address strOldRange = strNewRange ' get currently selected address strNewRange = Target.Address ' show old range address if it is being copied or cut If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then MsgBox strOldRange End If End Sub Put this code in a standard module: Public strNewRange As String Public strOldRange As String -- Cheers, Ryan "Aalamo" wrote: Hi, Can anyone confirm if there is a way in VBA to refer to a range that has just been copied but that it is not the selected range? For example: I select Range A1:A2, I press Ctrl+C and dotted moving lines appear around A1:A2. Now, I move to any other cell, say B4. At this stage, A1:A2 remain highlighted with the dotted lines, so I understand that the Application "knows" of them, therefore how can I return their address through code? Selection.address will obviously not work as it is no longer the selected range...I looked within the Application object but could not find anything... The fact that Excel knows that the range A1:A2 is the range that will be copied upon a paste operation, makes me think that the range is "active" in memory in some way and it could be retrirvable information, but perhaps it is one of those things the object model hides from us.. I tried reading the clipboard, but there you only get the values of those ranges, not the ranges as Range objects, with address property, formula property, etc... Any ideas? Many thanks Leo . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a range that has been copied (Excel-VBA)
On Jan 4, 2:49*pm, Ryan H wrote:
I'm not sure if there is a way to access the range properties from the clipboard. *This code will display the range address of the range you are copying or cutting. *Hope this helps! *If so, let me know, click "YES" below. Put this code in the Worksheets SelectionChange Event: Private Sub Worksheet_SelectionChange(ByVal Target As Range) * * ' get previously selected range address * * strOldRange = strNewRange * * ' get currently selected address * * strNewRange = Target.Address * * ' show old range address if it is being copied or cut * * If Application.CutCopyMode = xlCut Or Application.CutCopyMode = xlCopy Then * * * * MsgBox strOldRange * * End If End Sub Put this code in a standard module: Public strNewRange As String Public strOldRange As String -- Cheers, Ryan "Aalamo" wrote: Hi, Can anyone confirm if there is a way in VBA to refer to a range that has just been copied but that it is not the selected range? For example: I select Range A1:A2, I press Ctrl+C and dotted moving lines appear around A1:A2. Now, I move to any other cell, say B4. At this stage, A1:A2 remain highlighted with the dotted lines, so I understand that the Application "knows" of them, therefore how can I return their address through code? Selection.address will obviously not work as it is no longer the selected range...I looked within the Application object but could not find anything... The fact that Excel knows that the range A1:A2 is the range that will be copied upon a paste operation, makes me think that the range is "active" in memory in some way and it could be retrirvable information, but perhaps it is one of those things the object model hides from us.. I tried reading the clipboard, but there you only get the values of those ranges, not the ranges as Range objects, with address property, formula property, etc... Any ideas? Many thanks Leo .- Hide quoted text - - Show quoted text - Ryan, many thanks for the time and the idea. Unfortunately it is not what I require. In your code a user can select a range, then copy, and then select another range. At this stage your code tells you what the address of the one in copy is, i.e. the first selected range's address. So far so good. The problem is that when you select a third range you lost the reference to the original one being copied and instead you change provide the address of the second one your selected, i.e. you only remember the previously selected range, not the one that has been Copied - which is what I need. Basically, I need to retrieve the address of the range that will be copied, at any one time, whatever range is selected. Thank you Leo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a range that has been copied (Excel-VBA)
Aalamo wrote on 01/04/2010 08:32 ET :
Hi, Can anyone confirm if there is a way in VBA to refer to a range that has just been copied but that it is not the selected range? For example: I select Range A1:A2, I press Ctrl+C and dotted moving lines appear around A1:A2. Now, I move to any other cell, say B4. At this stage, A1:A2 remain highlighted with the dotted lines, so I understand that the Application "knows" of them, therefore how can I return their address through code? Selection.address will obviously not work as it is no longer the selected range...I looked within the Application object but could not find anything... The fact that Excel knows that the range A1:A2 is the range that will be copied upon a paste operation, makes me think that the range is "active" in memory in some way and it could be retrirvable information, but perhaps it is one of those things the object model hides from us.. I tried reading the clipboard, but there you only get the values of those ranges, not the ranges as Range objects, with address property, formula property, etc... Any ideas? Many thanks Leo Here's how I trap the copied range in Excel 2000: Usiing PERSONAL.XLS In module: Global grngCopied As Range In ThisWorkBook: Private WithEvents App As Application ________________________________________ Private Sub Workbook_Open() |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referring to a range that has been copied (Excel-VBA)
rijkstra wrote on 01/31/2012 17:44 ET :
Aalamo wrote on 01/04/2010 08:32 ET : Hi, Can anyone confirm if there is a way in VBA to refer to a range that has just been copied but that it is not the selected range? For example: I select Range A1:A2, I press Ctrl+C and dotted moving lines appear around A1:A2. Now, I move to any other cell, say B4. At this stage, A1:A2 remain highlighted with the dotted lines, so I understand that the Application "knows" of them, therefore how can I return their address through code? Selection.address will obviously not work as it is no longer the selected range...I looked within the Application object but could not find anything... The fact that Excel knows that the range A1:A2 is the range that will be copied upon a paste operation, makes me think that the range is "active" in memory in some way and it could be retrirvable information, but perhaps it is one of those things the object model hides from us.. I tried reading the clipboard, but there you only get the values of those ranges, not the ranges as Range objects, with address property, formula property, etc... Any ideas? Many thanks Leo Here's how I trap the copied range in Excel 2000: Usiing PERSONAL.XLS In module: Global grngCopied As Range In ThisWorkBook: Private WithEvents App As Application ________________________________________ Private Sub Workbook_Open() |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copied sheet causing duplicate range name error (Excel 2010) | Excel Discussion (Misc queries) | |||
Referring to specific cell in named range in Excel formula | Excel Worksheet Functions | |||
Trouble referring to a (dynamic) named range on another Excel shee | Excel Programming | |||
Copied range from excel to word (but not in table-form) | Excel Programming | |||
need to use vba to refer that if a checkbox is checked.. the contents beside it will be copied to another range - to add to excel form | Excel Programming |