move to the right after hitting enter using macros

[Santa Fe Al]

Hi All,

I am attempting to cause movement 4 cells to the right of present location
after I enter data and press enter. I had thought a Macro would do this, but
I can't tie a Macro to a cell and a separate button always takes me to the
original cell. How can I make the movement go right four cells and wait for
input after hitting enter in the previous cell?

Thanks, Al

 

[Gary''s Student]

This example is coded for cell B9, but it can be modified for any set of
cells. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("B9")
If Intersect(Target, r) Is Nothing Then Exit Sub
Range("F9").Select
End Sub

Once the macro has been installed, entering data in B9 will cause you to
jump to F9.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

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

To learn more about Event Macros (worksheet code), see:

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

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit
Application.EnableEvents = False
Target.Offset(0, 4).Select
endit:
Application.EnableEvents = True
End Sub

This sheet event code. Right-click the sheet tab and "View Code"

Copy/paste into that module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Wed, 18 Nov 2009 10:26:01 -0800, Santa Fe Al <Santa Fe