move to the right after hitting enter using macros

S

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
 
G

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top