Macro to link selected cell to most recently edited cell.

[Jaegan]

Hey folks. Looking to make a very basic macro, but my VBA skills are below basic!

Essentially, I want a macro that when used will set the currently selected cell to reference to the cell I most recently edited. So for example, after I complete the sum in C9, I want to go to cell A1, run my macro, and have A1 generate the formula linking it to cell C9.

[TABLE="width: 500"]
<tbody>[TR]
[TD]=C9
[/TD]
[TD][/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]=SUM(C1:C3)
[/TD]
[/TR]
</tbody>[/TABLE]


The main challenge I think will be trying to get the references to work across tabs (e.g. adding in the !sheet references when needed). Any pointers you can provide would be much appreciated!

 

[Gary's Student]

In every worksheet, include the follow event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    LastChanged = "=" & Target.Parent.Name & "!" & Target.Address
End Sub

In a standard module include the following:

Public LastChanged As String
Sub CaptureLastChanged()
    Application.EnableEvents = False
        ActiveCell.Formula = LastChanged
    Application.EnableEvents = True
End Sub

So after you have done some edits, run CaptureLastChanged to settup the activecell

 

[Jaegan]

Thanks a bunch. I will fiddle with this a bit later today and let you know if I run into any issues.

It sounds like there is no way around having to include the macro event in each tab if I want this to work. Would I be correct that even if we were to limit the macro functionality to only link to a cell within a specific tab ( no cross tab linking), we would still need the event macro in order to flag the most recently edited cell?

 

[Gary's Student]

The event macro needs to be included only in those tabs in which you wish to capture the last cell edited.