set cursor position after cell is populated??

[Robert Crandal]

If a user enters an integer value into say cell A1 and then presses the
Enter key, the cursor (or "active cell") will instantly move one cell
downwards, which I think is Excel's default behavior on spreadsheets.

Can I change this behavior using Visual Basic?? Basically, after a user
enters a value into an arbitrary cell and then presses the Enter key, I
would
like to program Excel to move the cursor or "active cell" to a cell of my
choice.

I hope this question makes sense. Please advise!

Thank you!

 

[Roger Govier]

Hi Robert

Something like the following should work
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myrng As Range
Set myrng = Range("C3") '<--- Change to suit source cell
If Not Intersect(Target, myrng) Is Nothing Then
Range("H3").Activate ' <-- Change to suit destination required
End If
End Sub

--
Regards
Roger Govier

If a user enters an integer value into say cell A1 and then presses the
Enter key, the cursor (or "active cell") will instantly move one cell
downwards, which I think is Excel's default behavior on spreadsheets.

Can I change this behavior using Visual Basic?? Basically, after a user
enters a value into an arbitrary cell and then presses the Enter key, I
would
like to program Excel to move the cursor or "active cell" to a cell of my
choice.

I hope this question makes sense. Please advise!

Thank you!



__________ Information from ESET Smart Security, version of virus
signature database 4536 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4536 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Barb Reinhardt]

You can do this with a worksheet_Change event.

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Patrick Molloy]

Sub StopMovingAfterEnter()
Application.MoveAfterReturn = False
End Sub

 

[Patrick Molloy]

in addition to my prev posting which just stopped the movement, you could use
the change event to control it ...here's some code that offers you some
ideas. right click the sheet tab and select View Code, then paste this:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count = 1 Then
If Target.Column = 3 Then
Select Case Target.Value
Case Is < 10
Range("B3").Select
Case 11
Worksheets("sheet2").Activate
Worksheets("sheet2").Range("C5").Select
Case 14, 21, 28
Range("A77").Select
Case Else
' do nothing
End Select

End If

End If


End Sub

 

[Robert Crandal]

Can I paste this code into ANY module in my Excel/Visual Basic code??
I only have one module ("Module 1"), in which I pasted this code exactly,
however it does NOT work. Am I missing something??

Sorry, I'm still a newbie at this Visual Basic for Excel stuff. thank you!
8)

 

[Robert Crandal]

Ok... I pulled the code out of my "Module 1". I tried pasting the code into
my
"Sheet1" object area and the code worked fine. 8)

After I pasted your code, I noticed a popup message that stated
"Cannot perform the requested operation" or something similiar to that.
Oh well, I guess it's nothing to worry about, right?? The code actually
works, I was just wondering why I got that message. Oh, I wont worry about
it too much!

 

[Roger Govier]

Hi Robert

I'm sorry. I should have included my normal instructions on how to paste the
code into the Sheet area, as it is event code.
Glad you figured it out and did the right thing.
I don't understand the message you got.
If you wanted to mail me a copy of your workbook, I would be happy to see if
I can resolve the issue for you.
to mail direct
roger at technology4u dot co dot uk
Do the obvious with at and dot to make a valid email address.

--
Regards
Roger Govier

Ok... I pulled the code out of my "Module 1". I tried pasting the code
into my
"Sheet1" object area and the code worked fine. 8)

After I pasted your code, I noticed a popup message that stated
"Cannot perform the requested operation" or something similiar to that.
Oh well, I guess it's nothing to worry about, right?? The code actually
works, I was just wondering why I got that message. Oh, I wont worry
about
it too much!




__________ Information from ESET Smart Security, version of virus
signature database 4537 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4537 (20091023) __________

The message was checked by ESET Smart Security.

http://www.eset.com