Worksheet_change & using tab

[mudstuffin]

I have a sub that triggers on the worksheet change event. However, it only seems to work on entering data into a cell and then hitting the enter key, whereas I need it to update on entering the information and then tabbing across to the next cell....

How can I do this, or is there something I'm missing.

Thanks


Mudstuffin

 

[Loomah]

Do you HAVE to tab accross or could you use the following to change the direction the cursor moves after hitting enter

In you data sheet's code module add the following:

Option Explicit

Private Sub Worksheet_Activate()
Application.MoveAfterReturnDirection = xlToRight
End Sub

Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub

And to avoid confusion add this to the workbook module:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.MoveAfterReturnDirection = xlDown
End Sub

If you don't know what I mean about confusion here try leaving this last sub out then changing to a different workbook from your data sheet after applying the first sub!

 

[mudstuffin]

Thanks for the reply.

I tried this, and this method of moving to the right works well. The trouble is, is that it seems to stop my sub from running.

The sub checks the code that the user inputs and then cross checks it with another sheet, and if the match is there, it pulls the value from the cell to the right of the code found on the data sheet and places it next to where the code is entered.

I can't see how this stops, as the sub is triggered from worksheet_change, and the code you provided kicks off from sheet_activate.

Any ideas why it stops....?

Thanks again.


mudstuffin

 

[euskadi]

Mudstuffin,

You can accomplish the same thing as your routine with a simple look-up. I'm wondering why you choose to use VBA?

One issue I ran into using worksheet_change is that it can become circular... the worksheet_change event runs, changes the sheet, and triggers itself to run again. I just try to avoid it if I can.

 

[Loomah]

Mudstuffin
I'm at a loss as to why your sub won't run. I've just added the following meaningless test to the same sheet to which the change direction is applied with no problems.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Something Happened"
End Sub

Incidentally it fired ok when I tabbed rather than using enter!

euskadi has made some very valid points which may save you a lot of grief!