PDA

View Full Version : Solved: Goto previous cell



wpanssi
08-21-2009, 03:21 PM
I want to make a macro that goes to previous cell that user visited. I found this: http://www.archivum.info/microsoft.public.excel.programming/2006-05/05500/Re:_go_back_previous_cell

This solution is based on a Worksheet_SelectionChange -sub.
However, this solution works only for one worksheet. I need to make the macro work in any sheet or in any workbook.

Is this possible? How?

Thanks in advance!

rbrhodes
08-21-2009, 06:17 PM
Hi,

Put this in the ThisWorkbook Module and kill the sheet module code.


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = NewCell
Set NewCell = Target
End If

End Sub

tpoynton
08-21-2009, 07:03 PM
and if the last cell was on a different worksheet (not well tested):

in thisworkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If OldCell Is Nothing Then
Set OldCell = Target
Set oldSht = Sh
Else
Set OldCell = NewCell
Set oldSht = newSht
Set NewCell = Target
Set newSht = Sh
End If
End Sub

module:

Option Explicit
Public OldCell As Range
Public NewCell As Range
Public oldSht As Worksheet
Public newSht As Worksheet
Public Sub GoBack()
oldSht.Activate
OldCell.Select
End Sub

installing as an addin would have it available to any workbook.

wpanssi
08-22-2009, 06:48 AM
Awesome! Thanks guys!