Selection the previously selected cell

[David]

I would like to know how to select the previously selected
cell in Excel using VBA code.

I am using the Worksheet_Change event to validate values
entered, using IF statements in VBA.

This works fine as long as the value is entered using the
enter key, as the active cell does not change. However,
if a value is entered using any of the arrow keys, the
active cell changes and the wrong cell is validated!

When the Worksheet_Change event is triggered I want to be
able to return to the cell into which the value has been
entered, regardless of which key has been used to enter
it. I still want to be able to use the arrow keys to move
about the worksheet.

Can anyone help?

 

[BrianB]

Use the Target variable supplied .. eg.

'--------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value = 1 Then
MsgBox ("Error")
End If
End Sub
'----------------------------------------

 

[Chip Pearson]

David,

The Target argument to the Worksheet_Change event procedure will
refer to the cell which was changed. In your code, ensure that
you are using Target, not ActiveCell. E.g.,


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Value > 10 Then
Target.Select
MsgBox "Invalid input"
End If
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[David]

Thanks chip,

You wouldn't believe how long I've spent trying to do
this!!!