Search code examples
excelvbamouse-cursor

Make cursor jump to same row, different column when pressing Enter, with other specific requirements

I've had a look about and whilst I've found some similar threads (with varying degrees of relevancy/usefulness), none of them covered my exact situation.

I want to use a sheet in my workbook as a form where I input one type of data in one column and press enter, and then jump to another column on the same row to input a second type of data.

My sheet has an "Item Number" column and a "Count" column with several columns in between them.

What I'm trying to achieve is:

  • Type data into a cell in the "Item Number" column and press Enter.
  • The cursor appears on the same row, several columns to the right in the "Count" column.

I want this only to occur when pressing Enter after entering data into blank cells only in the "Item Number" column. Pressing Enter in any other column should just allow the cursor to behave as it would otherwise.

At the moment I have the code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A100") = ActiveCell.Address
Cells(Application.ActiveCell.Row, 11).Select

End Sub

The first line (beginning with "Range") allows me to see the active cell address in cell A100. This works along with some other code that lets me colour fill the active cell in that worksheet so I can see things a bit better when the spreadsheet gets busy.

The second line (beginning with "Cells") is the code that is supposed to make the cursor jump over to the "Count" column after pressing Enter in the "Item Number" column.

Now, this all works fine except for one major drawback..... When clicking on a cell in the "Item Number" column, the cursor is immediately moved to the "Count" column before I'm able to enter any data, let alone hit the Enter key.

What am I doing wrong? and How do I go about ensuring this only happens when pressing Enter after entering data into a blank cell in the "Item Number" column?

I hope this makes sense, reading so much about code has got me cross eyed. Cheers

Solution

  • you should combine Worksheet_Change() and Worksheet_SelectionChange() events and use a sheet scoped variable to check for empty cells being changed

    Option Explicit

Dim emptyCell As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub ' don't bother nulticell selections
    If Cells(1, Target.Column).Value <> "Item Number" Then Exit Sub ' don't bother selections outside "Item Number" column
    If emptyCell And Not IsEmpty(Target.Value) Then Cells(Target.Row, Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Find(what:="Count", LookIn:=xlValues, lookat:=xlWhole).Column).Select ' if current cell was empty and now it is not then skip to "Count" column same row
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    emptyCell = IsEmpty(Target.Value) ' store the info about current selection being empty
End Sub