Macro for returning to the previously selected cell

[Mike C]

Hello - I am trying to figure out how to create a macro that will
allow me to jump to my prior location in a workbook.

For example, I will be in Sheet1:A35, then I will need to go to
Sheet573. What I am trying to do is come up with a shortcut to
immediately jump back to Sheet1:A35.

Does anyone have some code put together that does this?

Thanks for any suggestions!

- F

 

[Mark Ivey]

Not real sure if this will do what you want, but because Excel does not have
a "BeforeChange" event it can be somewhat complicated to do what you are
wanting to do.

Put the following inside "ThisWorkbook" and watch what happens as you move
from sheet to sheet with the Message Box prompts. Maybe something here will
work or maybe you can figure out what you need from here.

Sorry not to be of much help on this topic...

Mark Ivey

'**** Code Starts Here ****
Private wbOpenRange As Variant
Private shActivateRange As Variant
Private selChgRange As Variant

Private Sub Workbook_Open()
wbOpenRange = ActiveSheet.Name & "!" & ActiveCell.Address
test
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
shActivateRange = Sh.Name & "!" & ActiveCell.Address
test
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
selChgRange = Sh.Name & "!" & ActiveCell.Address
test
End Sub


Sub test()
If wbOpenRange = "" Then
wbOpenRange = "Nothing"
End If
If shActivateRange = "" Then
shActivateRange = "Nothing"
End If
If selChgRange = "" Then
selChgRange = "Nothing"
End If

MsgBox "ON OPEN EVENT: " & wbOpenRange & vbCrLf & vbCrLf _
& "SHEETACTIVEATE EVENT: " & shActivateRange & vbCrLf & vbCrLf _
& "SELECTIONCHANGE EVENT: " & selChgRange
End Sub

 

[Dave D-C]

I'm kind of using Mark's idea here, but how about:
In ThisWorkbook:

Option Explicit

Dim gNewTarget As Range, gOldTarget As Range

Private Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)
Set gOldTarget = gNewTarget
Set gNewTarget = Target
End Sub

Public Sub Goback()
Sheets(gOldTarget.Parent.Name).Activate
gOldTarget.Select
End Sub

Now you still have to connect the shortcut to
the Sub Goback. Dave D-C

 

[Rick Rothstein \(MVP - VB\)]

Give the following a try. Add a Module to your project and copy/paste these
declarations into its code window...

Public OldCell As Range
Public OldSheet As Object

Next, double click the ThisWorkbook entry in the Project list and copy/paste
this code into its code window....

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
Set OldCell = Target
Set OldSheet = Sh
End Sub

Finally, use the following in your own subroutine or function...

Sub YourSubroutine()
Dim SavedOldSheet As Object
Dim SavedOldCell As Range
Set SavedOldCell = OldCell
Set SavedOldSheet = OldSheet
'
' <<< Place your own code here >>>
'
SavedOldSheet.Activate
SavedOldSheet.Range(SavedOldCell.Address).Select
End Sub


Rick