Hi,

I want to capture the last cell data was entered so I can validate it and
return the cursor there if required. The user can 'Enter' or 'Arrow' away.

Any ideas?
--
Andrew

use the CHANGE event for the sheet....right click the tab and select the code
page. This is the sheet's code page....then you can code the proc below,
where Target is the cell hat has changed. Caution....if the procedure changes
the value of any cell, then it will call itself...thats ok if the resultant
call doesn't make any more changes. If it does, then you'll set off a
recursive loop....so set Application.EnableEvents = False to turn off event
handling first!.
Example:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' test for a given range, where the values must be 1< 10
If Not Intersect(Range("InputArea"), Target) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value

Case Is < 1
Target.Value = 1
Case Is 10
Target.Value = 10
End Select
Application.EnableEvents = True
End If
End Sub


"Andrew" wrote:

Hi,

I want to capture the last cell data was entered so I can validate it and
return the cursor there if required. The user can 'Enter' or 'Arrow' away.

Any ideas?
--
Andrew

Sorry but I don'tt think this is the answer I'm after.

The user enters directly into a spreadsheet (don't want to use a form) in
various locations. If the user keys in a particular range "K53:K58" I use
Worksheet_Change to do some some data validation. Thie validation works fine
but the cursor remains away from the cell in error.

I want to return to the cell in error.
--
Andrew


"Patrick Molloy" wrote:

use the CHANGE event for the sheet....right click the tab and select the code
page. This is the sheet's code page....then you can code the proc below,
where Target is the cell hat has changed. Caution....if the procedure changes
the value of any cell, then it will call itself...thats ok if the resultant
call doesn't make any more changes. If it does, then you'll set off a
recursive loop....so set Application.EnableEvents = False to turn off event
handling first!.
Example:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' test for a given range, where the values must be 1< 10
If Not Intersect(Range("InputArea"), Target) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value

Case Is < 1
Target.Value = 1
Case Is 10
Target.Value = 10
End Select
Application.EnableEvents = True
End If
End Sub


"Andrew" wrote:

Hi,

I want to capture the last cell data was entered so I can validate it and
return the cursor there if required. The user can 'Enter' or 'Arrow' away.

Any ideas?
--
Andrew