Return to last active cell in range, ignoring any active cell outside of specified range?-CodePudding

Is it possible to use VBA to create a macro that selects the last active cell in a specified range? For instance, if my defined range is A1:A10, I select A2, then randomly select a few cells outside of my previously defined range, how to can I use VBA to return to A2? Further, if after I reselected A2, and now moved down to A5, how would I return back to A5 at a later date using my macro?

Code tried:

Sub PreviousCell()
Dim PrevCell As Range
Range("A2").Select
Set PrevCell = ActiveCell
Range("A1").Select
MsgBox "Previous cell was " & PrevCell.Address & Chr(10) & "Active cell is " & ActiveCell.Address
End Sub

Any help is appreciated. My VBA knowledge is highly limited. Thank you very much!

CodePudding user response：

I figured it out myself. I am not sure this is the cleanest method, but it worked for me.

I created a Global Variable in Module 1:

Option Explicit

Global LastCell As Range
Global ReturnToCell As Range

In Module 2 I created a Sub that I could call later with a shortcut:

Sub CallActive()

     ReturnToCell.Select

End Sub

In Sheet1 I wrote:

Sub Worksheet_SelectionChange(ByVal Target As Range)

     Dim LastCell As Range
     Set LastCell = Range("[YOUR RANGE HERE]")

     If Not Intersect(ActiveCell, LastCell) Is Nothing Then
          Set ReturnToCell = ActiveCell
     End If

End Sub

I then assigned the macro CallActive to ctrl t. This whole process means that if I select any cell in the specified ranged, then select any cell outside of the specified range, I can press ctrl t to return to the last active cell in my specified range.