Run Macro after cell content changes...
 

Hello fellow programmers! I am in need of a macro that will simply jump
to another location on a spreadsheet after the end-user types anything
in a particular cell... I would like it so that if someone types an
answer for a question in cell A2 a macro will fire causing the
spreadsheet to then select cell A6. Thanks!

Run Macro after cell content changes...
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Range("A6").Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hello fellow programmers! I am in need of a macro that will simply jump
to another location on a spreadsheet after the end-user types anything
in a particular cell... I would like it so that if someone types an
answer for a question in cell A2 a macro will fire causing the
spreadsheet to then select cell A6. Thanks!

Run Macro after cell content changes...
 

My thanks to both of you, for asking the question and for the answer. I have
been agonizing over a scenario like this for days!

I need to continue this process of moving to a new cell after input through
about 60 additional cells. Do I need to repeat the sequence with new sub
routines, target names, and so forth for each cell I want to move to/from.

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Range("A6").Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hello fellow programmers! I am in need of a macro that will simply jump
to another location on a spreadsheet after the end-user types anything
in a particular cell... I would like it so that if someone types an
answer for a question in cell A2 a macro will fire causing the
spreadsheet to then select cell A6. Thanks!

Run Macro after cell content changes...
 

Absolutely not, there is only one Change event per sheet.

Minimum is to change the constant WS_RANGE to your target range of cells

If it is a fixed movement, say 1 cell right, 2 cells down, it is simple,
just change

Me.Range("A6").Select

to
.Offset(2,1).select

If it is more complex, taht is thereis not pattern, use something like

Select Case .Address(0,0)
Case "A2": Me.Range("A6").elect
Case "B1": Me.Range("H17").Select
'etc
End Select

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JR Hester" wrote in message
...
My thanks to both of you, for asking the question and for the answer. I
have
been agonizing over a scenario like this for days!

I need to continue this process of moving to a new cell after input
through
about 60 additional cells. Do I need to repeat the sequence with new sub
routines, target names, and so forth for each cell I want to move to/from.

"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Range("A6").Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hello fellow programmers! I am in need of a macro that will simply
jump
to another location on a spreadsheet after the end-user types anything
in a particular cell... I would like it so that if someone types an
answer for a question in cell A2 a macro will fire causing the
spreadsheet to then select cell A6. Thanks!