none
How to track new RC coordinates after user cut/paste RRS feed

  • Question

  • I export the results of an ACCESS report into EXCEL.  The user uses this spreadsheet to establish the sequence in which competitors compete.  Most times the output from access is OK but sometimes Joe Jones has to be moved to another lane or has to compete in a different sequence.  I need to be able to track and import back into access when changes are made.  I have the event ID and competitor ID saved in a background sheet but need to update the background sheet when Joe is moved from lane 1 x to lane y and/or from competing 5th in a certain event to competing 7the in that event.

    When Joes name is moved on sheet1 I need to move his identifiers such as studentID and eventID to the corresponding cell in sheer2

    Ideas???

    Tuesday, March 8, 2016 2:53 AM

Answers

  • Ok, here's the code. Note that you need to adapt it for different error situations that only you know can occur.

    Put this code in ThisWorkbook:

    Option Explicit
    '*************************************** '* On startup '* Private Sub Workbook_Open() Application.EnableEvents = True End Sub

    Put this code in Sheet1 (Names):

    Option Explicit
    Dim g_move(2) As Range    'Global variable to hold from and to cells
    '************************************************************************
    '* Fires when a cell is changed
    '* Note: The code must be in the worksheet it targets,
    '* not in ThisWorkbook or in a module
    '* For a Cut and Paste operation the code will fire twice in a row,
    '* when the paste is done, one for the cut and the other for the paste.
    '*
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim WS As Worksheet
    
    ' Debug.Print Target.Address & " " & Target.Value
    
        If Target.Value = "" Then    'Cut operation
            Set g_move(0) = Target
        Else                    'Paste operation
            Set g_move(1) = Target
            '*
            '* Now mirror the cut and paste on the ID worksheet
            '*
            Set WS = ThisWorkbook.Worksheets("IDs")
            WS.Range(g_move(0).Address).Cut Destination:=WS.Range(g_move(1).Address)
    
        End If
    End Sub
    When you move (cut and paste) a person in the Names worksheet the corresponding ID will be moved to the corresponding target cell in the IDs worksheet.


    Best regards, George


    Thursday, April 7, 2016 3:18 PM

All replies

  • Hi, Steve Staab

    can  you tell us in which situation "Joe Jones" moved to another lane? can you provide your report to check it how its changed and how can we stop that .

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Tuesday, March 8, 2016 8:03 AM
    Moderator
  • Joe's name is moved on purpose by the user.   suppose Joe's name in in cell C-4 sheet1 and his ID number is in the corresponding cell of sheet2.  the user only sees the name and may move the name to a different cell such as D-7.

    How do I capture this action and move Joe's ID to cell D-7 on sheet2?  Is there some 'sheet synchronizing' function?

    After the user has made all the 'moves' needed they run a macro that exports the cell coordinates of the ID numbers back into Access so reports and score sheets are printed in the correct order.

    Tuesday, March 8, 2016 4:09 PM
  • Is there a way to 'capture' the before and after Row/Column coordinates when a user cuts/pastes the contents of one or more cells into a new location?   I need these coordinates so I can initiate another macro to move related cells on another worksheet.
    Tuesday, March 15, 2016 7:18 PM
  • Hi, Steve Staab

    I will discuss this with other support engineers. As soon as I will get the solution I will provide to you.

    Regards

    Deepak 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Wednesday, March 16, 2016 7:15 AM
    Moderator
  • Hi, Steve Staab

    I find a lot but I did not get any useful information regarding that. I am stuck at to get the source and destination cells address when user perform Cut and Paste operation in sheet1. so after that I can use that source and destination address to change the value in sheet2. but I am not succeeded with VBA. one of the possible work around is keyboard hooks. but it is part of Windows Development and it's not part of Office development.

    so if you also think that keyboard hook can solve your issue you can raise this question on Windows Development Forum.

    Regards

    Deepak   


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 4, 2016 9:44 AM
    Moderator
  • I have done a similar work on a spreadsheet exported from Access.

    I need an example of a "normal" and a "moved" such sheet in order to provide you with a feasible solution.


    Best regards, George

    Monday, April 4, 2016 2:26 PM
  • Below is sheetNames showing a list of names before and after the name Sue is moved (CUT_PASTE) from cell A7 to B2

    When the name Sue is moved on sheet Names from A7 to B2 I want her ID on sheet IDs to automatically move from A7 to B2.

    Wednesday, April 6, 2016 5:26 PM
  • Ok, here's the code. Note that you need to adapt it for different error situations that only you know can occur.

    Put this code in ThisWorkbook:

    Option Explicit
    '*************************************** '* On startup '* Private Sub Workbook_Open() Application.EnableEvents = True End Sub

    Put this code in Sheet1 (Names):

    Option Explicit
    Dim g_move(2) As Range    'Global variable to hold from and to cells
    '************************************************************************
    '* Fires when a cell is changed
    '* Note: The code must be in the worksheet it targets,
    '* not in ThisWorkbook or in a module
    '* For a Cut and Paste operation the code will fire twice in a row,
    '* when the paste is done, one for the cut and the other for the paste.
    '*
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim WS As Worksheet
    
    ' Debug.Print Target.Address & " " & Target.Value
    
        If Target.Value = "" Then    'Cut operation
            Set g_move(0) = Target
        Else                    'Paste operation
            Set g_move(1) = Target
            '*
            '* Now mirror the cut and paste on the ID worksheet
            '*
            Set WS = ThisWorkbook.Worksheets("IDs")
            WS.Range(g_move(0).Address).Cut Destination:=WS.Range(g_move(1).Address)
    
        End If
    End Sub
    When you move (cut and paste) a person in the Names worksheet the corresponding ID will be moved to the corresponding target cell in the IDs worksheet.


    Best regards, George


    Thursday, April 7, 2016 3:18 PM
  • Hi, Steve Staab

    >>George has provided you a very simple solution to your issue. I have tested it and it is working correctly.

    >> I was finding the solution to get cut and paste event but after this solution we need not to do like that.

    >> here I want to suggest you to click on “Mark as Answer” on the suggestion provided by the George.

    >> Thank you George with your solution now we are able to solve this issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 8, 2016 6:43 AM
    Moderator