Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo cell..
Hi, I have a Private Sub Worksheet_Change(ByVal target As Range) macro which
does something when value changes in a particular cell.. But when user input any value they may press right arrow or down arrow.. Is there a way to go back to cell in which value was entered recently? as I need to perform some tasks over there.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo cell..
Here is an example of detecting a change in B9, doing something, and then
returning to B9: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("B9") If Intersect(r, Target) Is Nothing Then Exit Sub MsgBox ("back we go!") r.Select End Sub -- Gary''s Student - gsnu200850 "Kashyap" wrote: Hi, I have a Private Sub Worksheet_Change(ByVal target As Range) macro which does something when value changes in a particular cell.. But when user input any value they may press right arrow or down arrow.. Is there a way to go back to cell in which value was entered recently? as I need to perform some tasks over there.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo cell..
Do you mean go back to the cell just changed? If so:
Private Sub Worksheet_Change(ByVal Target As Range) 'go back if value = 3 If Target.Value = 3 Then Range(Target.Address).Activate End If End Sub Note that the above code refers to Range(Target.address). You can make changes or go to other cells using this. Example 1, go two cells to the right of target : Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = 3 Then Range(Target.Address).Offset(, 2).Activate End If End Sub Example 2, multiply the value by 2: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value = 3 Then 'stay in the new address 'change the old value Range(Target.Address).value = Range(Target.Address)*2 End If End Sub Almost forget to mention: You need to wrap your code in an if condition to ensure it doesn't activate when a range of cells are deleted or updated at the same time (paste, drag & drop, etc) Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 Then If Target.Value = 3 Then Range(Target.Address).Value = Range(Target.Address) * 2 End If End If End Sub -- Steve "Kashyap" wrote in message ... Hi, I have a Private Sub Worksheet_Change(ByVal target As Range) macro which does something when value changes in a particular cell.. But when user input any value they may press right arrow or down arrow.. Is there a way to go back to cell in which value was entered recently? as I need to perform some tasks over there.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
goto last cell with data | Excel Programming | |||
Goto last cell | Excel Programming | |||
How to goto the next available cell in a column. | Excel Programming | |||
Goto Next VISIBLE cell below | Excel Programming | |||
goto a particular cell | Excel Programming |