Auto-Select Cell After 10 Seconds

[Frith]

Hello there,

I would like to know how I could have Excel auto-select -- i.e., move the cursor to -- the last non-blank (>0) cell in column A, ten seconds after the last cell input/edit was made anywhere within the same WS? Is this possible, via any VBA code?

Thank you,
Frith

 

[Ralajer]

Frith,

The below code will do what you want but the drawback is that it won't allow editing for the ten seconds. If there is a way to do it and allowing editing that would be nice to know; however, I am not sure it can be done.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastColACell As Range
    Set LastColACell = Cells(Rows.Count, 1).End(xlUp)
    '// If the cell just changed is the same as the last non-Blank no wait.
    If Not Intersect(Target, LastColACell) Is Nothing Then Exit Sub
    Application.Wait Time + TimeSerial(0, 0, 10)
    LastColACell.Select
End Sub

 

[Frith]

Hey Ralajer, thanks & sorry for the delayed response... Actually, while that code "does" what it should, the "way" in which it does (i.e., precluding any edits, as you note) is what's precluding me from using it. Any one here have any ideas?

Thanx again,
Frith

 

[John_w]

Here's one way. All the code goes in the sheet's module.

Private RunWhen As Double

Private Sub Worksheet_Change(ByVal Target As Range)
       
    'Restart timer
    
    StopTimer
    StartTimer
    
End Sub

Private Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, 10)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=Me.CodeName & ".SelectCell", Schedule:=True
End Sub

Private Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=Me.CodeName & ".SelectCell", Schedule:=False
End Sub

Private Sub SelectCell()

    'Select last non-blank cell in column A
    
    Cells(Rows.Count, "A").End(xlUp).Select
    
End Sub

 

[Frith]

Sweet, thanks John_w

Frith