Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection the previously selected cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection the previously selected cell
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 '---------------------------------------- -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection the previously selected cell
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection the previously selected cell
Thanks chip,
You wouldn't believe how long I've spent trying to do this!!! -----Original Message----- 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" wrote in message ... 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I edit directly in a cell - previously F2 | Excel Discussion (Misc queries) | |||
Getting rid of previously typed data from a cell | Excel Discussion (Misc queries) | |||
Lookup Oldest date from previously selected Group Data | Excel Worksheet Functions | |||
Selection.End(xlDown) with Rows selected | Excel Programming | |||
Selection.End(xlDown) with Rows selected | Excel Programming |