VBA to Call Last Cell Changed RRS feed

  • Question

  • Hi..

    I am trying to refer to the cell that was last changed in value in my worksheet.. ActiveCell would work for some instances but if the user clicked enter or moved to another cell promptly after changing the cell that I want to refer to, ActiveCell fails to return the right cell I am looking for.. Can you please help me figure this out? Thanks!!

    Monday, November 23, 2015 11:00 PM

All replies

  • Copy the following code into the worksheets module (ie. Right click the worksheet tab name and select View Code)

    Dim rngLastChange As Range  'Must be at top of module before an subs

    Private Sub Worksheet_Change(ByVal Target As Range)
        Set rngLastChange = Target
    End Sub

    Sub test()
        MsgBox "Range last changed = " & rngLastChange.Address
    End Sub

    The above will only work with code that is within the worksheet module. If you want the Sub Test to work in another module and the variable must be dimensioned as Public at the top of a standard module. (ie. In the VBA editor select menu item Insert -> Module.

    Example for dimensioning in a standard module as public.

    Public rngLastChange As Range  'Must be at top of standard module before an subs

    Regards, OssieMac

    Tuesday, November 24, 2015 12:02 AM