VBA to move cursor when ENTER is pressed

[Wolfpacksr]

I have seen multiple threads with VBA code for moving the cursor, however, all seem to be based on data being entered in a cell. I have a form that sometimes data will be entered in the cell and sometimes the cell will be left blank. I need the cursor to move from cell to cell when ENTER is pressed whether data has been entered or not.

Also, sometimes I will go back to a certain cell and change or add data to that cell and then move from there. In other words, I may not have to go through the entire sequence of cells and then back to the beginning.

Here's the order of cells.

D5, D7, D10, F10, D11, F11, D12, F12, D16, D17, D18, L16, L17, L18, D26

Appreciate any help.

Using Office 2016. Spreadsheet is protected. (added 8/20)

[Leith Ross]

Hello Wolfpacksr,

Welcome to the Forum!

If you have a workbook then please post it. This reduces time in answering your question and errors in code solutions.

[mehmetcik]

Right Click On Your Sheet Name At the Bottom Of Excel and Select View Code
Paste this code in the module that opens and close it.

Please Login or Register  to view this content.

[Kenneth Hobson]

The usual approach is to use the worksheet Change event. Many have used the same or slightly modified version.

In my example file, the first worksheet used mehmetcik's method.

Worksheet 2 uses the traditional method with my added concept. That is, to move from a cell not changed, one must change it to fire the Change event. I used Copy/Paste to do that via Application.OnKey. I dealt with the issue of protected/locked cells a bit. It is a bit annoying that for a first Enter key press in a protected cell or one not in the TabOrder set, one needs to press Enterkey a 2nd time. To move, it restores the Enterkey and uses SendKeys() to send the key again. While I don't like it, it "sort of" works. The worksheet code relies on the module code as well.

[Wolfpacksr]

mehmetcik, thanks for your reply. Unfortunately, it did not work for me. It works first time through, but once it goes back to the first cell (D5) it no longer works. Also, it doesn't allow me to click out of the sequence. So if I type something wrong in D12, I can't go back and correct it. Every time I click on D12, it continues running through the sequence (array).

[Marc L]

Next demonstration works as it is whatever a 32 bits or a 64 bits Excel 2010 and upper version.
For previous versions just remove the PtrSafe statement …

According to the initial post and guessing it's an unprotect worksheet (or the code should be easier !) a starter demonstration :

Private Declare PtrSafe Function GetKeyState% Lib "User32.dll" (ByVal nVirtKey&) 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
 Static A$, SP$() 
    Dim K% 
        K = GetKeyState(13) 
    If A = "" Then 
            SP = Split("D5,D7,D10,F10,D11,F11,D12,F12,D16,D17,D18,L16,L17,L18,D26", ",") 
        If K < 0 Then 
            If IsNumeric(Application.Match(Target(0).Address(False, False), SP, 0)) Then 
                A = Target(0).Address(False, False) 
            Else 
                K = 0 
            End If 
        End If 
    End If 
    If K < 0 Then 
        If IsError(Application.Match(Target(0).Address(False, False), SP, 0)) And _ 
           IsNumeric(Application.Match(Target.Address(False, False), SP, 0)) Then 
            A = Target.Address(False, False) 
        Else 
            K = Application.Match(A, SP, 0) 
            A = SP(-(K < UBound(SP) + 1) * K) 
            Application.EnableEvents = False 
            Range(A).Select 
            Application.EnableEvents = True 
        End If 
    ElseIf IsNumeric(Application.Match(Target.Address(False, False), SP, 0)) Then 
        A = Target.Address(False, False) 
    End If 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[Marc L]

New version in previous post …

[Wolfpacksr]

Ken, thanks for the reply. Your program worked for the most part, but had some trouble copying it to my spreadsheet. It would look for your spreadsheet each time. I had to add my info to your spreadsheet. The code doesn't start until I select sheet 2 and then go back to sheet 1.

[Wolfpacksr]

Marc, thanks for the reply. I haven't had a chance to try it yet, but my spreadsheet is protected, will this change things?

Just tried it. Doesn't work on protected spreadsheet. Sorry, I should have stated that previously.

[Marc L]

As it well works too on my side on a protected sheet, no issue with a smart one …

The reason why we asked for an attachment reflecting exactly the real one !
As that needs some ajustments I won't even try to guess without any attachment
but you can easily amend my code to fit your need with your worksheet …

[Wolfpacksr]

File attached.
I had to remove some confidential info. Spreadsheet is protected. Sheet2 is for Lookup functions. Cells in red are for formulas and will be hidden. I've add/removed cells since the original post, so cell sequence has change.
Cell Sequence = (D4, D6, D9, F9, D10, F10, D11, F11, D15, D16, D17, D18, D19, D20, D21, L15, L16, L17, D25, Q14, Q21).

[Kenneth Hobson]

Marc's macro works fine for both protected and unprotected worksheets and locked/unlocked cells. Since it is triggered by the worksheet Selection event, you must enable the Select Locked Cells option when you Protect that worksheet.

Paste your new key sequence between his quotes for this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

My method is not meant for protected/locked cells in the taborder array as it is designed to Change the cell to trigger the macro. As such, one should protect the sheet with Select Locked Cells option unchecked.

Marc's method can likely be modified to handle the Tab and Shift+Tab order as well.

[Wolfpacksr]

It now runs through the cell sequence, but I can't get out of the sequence, I have to run through the full sequence. If I'm in D16 and don't have any more information to enter I can't go to D4 and start over. It continues in the sequence where I left off. So, if I enter data in D4, when I press Enter the cursor jumps to D17.

[Marc L]

According to your attachment as it is, 3 steps you must follow :

• 3 : once both codes in their respective class modules, save the workbook as .xlsb binary format and close it.
  
  
• 2 : paste this code to the ThisWorkbook module :

Private Sub Workbook_Open() 
    Sheet1.Initialize 
End Sub  

• 1 : paste next code to the Sheet1 worksheet module :

Private Declare PtrSafe Function GetKeyState% Lib "User32.dll" (ByVal nVirtKey&) 
 
Dim A$, S$() 
 
Sub Initialize(Optional C$) 
        Me.Activate 
    If C > "" Then 
        If Range(C).Locked = False Then 
            Application.EnableEvents = False 
            Range(C).Select 
            Application.EnableEvents = True 
        End If 
    End If 
        A = Selection(1).Address(False, False) 
        S = Split("D4,D6,D9,F9,D10,F10,D11,F11,D15,D16,D17,D18,D19,D20,D21,L15,L16,L17,D25,Q14,Q21", ",") 
End Sub 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        Dim P% 
    If GetKeyState(13) < 0 Then 
            P = Application.Match(A, S, 0) 
            A = S(-(P < UBound(S) + 1) * P) 
        If Target(1).Address(False, False) <> A Then 
            Application.EnableEvents = False 
            Range(A).Select 
            Application.EnableEvents = True 
        End If 
    Else 
        A = Target(1).Address(False, False) 
    End If 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[Wolfpacksr]

Thanks Marc, works great!!