Moving back to where you were before press ENTER

Topic

New Reply

I have a macro I have used for years in Excel that works just fine, except for one small usability issue. Before I run the macro, I have to manually select the cell that I last edited (i.e. where I was before I pressed ENTER). In some cases, this is one row up and three columns to the left. In other cases, it is just one row up. In rare cases, it is something different.

Is there some way in an Excel macro to tell Excel to move back to the last cell I edited before the macro does the rest of its magic?

Thanks!!
Troy

Reply | Quote

Replies

Is there a way to tell, based on the active cell, where the last edited cell is?

If not, excel would have to keep track of the last edited cell. This would disable UNDO and could make XL sluggish. Is the overhead and loss of UNDO for XL to store the location of the last edited cell after each change better then the annoyance of selecting the cell?

Steve

Reply | Quote

Troy,

You could try and see if the following snippet of code (in the code module behind the sheet you wish to monitor), captures what you are trying to trap, and then adapt to your precise requirements:

—————————— VBA code: ———————————————————————-

Option Explicit

Dim last_edited_cell As Range

Private Sub Worksheet_Change(ByVal Target As Range)

Set last_edited_cell = Target

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim usr_msg As VbMsgBoxResult

If Not last_edited_cell Is Nothing Then
usr_msg = MsgBox(“new selection (target) is ” & Target.Address & Chr(10) & _
“last edited cell: ” & last_edited_cell.Address & Chr(10) & _
“Select last edited cell?”, vbQuestion + vbYesNo)
If usr_msg = vbYes Then
Application.EnableEvents = False
last_edited_cell.Select
Application.EnableEvents = True
End If
End If

End Sub

——————— end of VBA code ————————————-

(is there an easy way to make the editor “show VBA code” – indents, mono-spaced font etc…)

Richard (from Piracicaba).

Reply | Quote