Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    New Lounger
    Join Date
    May 2010
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •