Using Enter button to move to next data entry cell and Backspace to go to last data entry cell

[QueenS]

Would be most appreciative of any help in solving problem that titles this post. Entry cells are placed over worksheet but have a sequence to it. Solutions I've seen so far include selection of the cell range and tabbing through it, using the excel form feature for user entry but I do not want these approaches. The worksheet opens up with selection on the 1st user input cell (I've got this part covered). Then upon pressing Enter, the selection jumps to next input cell in the sequence. Unless cursor is in cell for active typing, pressing Backspace selects prior input cell for user.

Think a macro would work best for this. Please help.

 

[dmt32]

Hi,
Sounds like a Tab Order solution I developed with Jerry Sullivan MVP on this board may do what you want.

Place ALL following code in a STANDARD module:

 Public Const TabSheet As String = "TabSheetName"

 Sub SetOnkey(ByVal state As Integer)
 '  Ver 2 2014
 ' Authors Dave Timms (aka DMT32) and  Jerry Sullivan MVP


    If state = xlOn Then
        With Application
            .OnKey "{TAB}", "'TabOrder xlNext'"             'Tab key
            .OnKey "+{TAB}", "'TabOrder xlPrevious'"        'Shift + Tab Key
            .OnKey "~", "'TabOrder xlNext'"                 'Enter Key
            .OnKey "{RIGHT}", "'TabOrder xlNext'"           'Right Arrow Key
            .OnKey "{LEFT}", "'TabOrder xlPrevious'"        'Left Arrow Key
            .OnKey "{BACKSPACE}", "'TabOrder xlPrevious'"   'BACKSPACE Key
            .OnKey "{DOWN}", "do_nothing"
            .OnKey "{UP}", "do_nothing"
        End With
    Else
    'reset keys
        With Application
            .OnKey "{TAB}"
            .OnKey "~"
            .OnKey "{RIGHT}"
            .OnKey "{LEFT}"
            .OnKey "{BACKSPACE}"
            .OnKey "{DOWN}"
            .OnKey "{UP}"
        End With
    End If
End Sub


Sub do_nothing()
'nothing to do
End Sub


Sub TabOrder(ByVal Direction As XlSearchDirection)
'  Ver 2 2014
 ' Authors Dave Timms (aka DMT32) and  Jerry Sullivan MVP


    Dim m As Variant, i As Long


    On Error Resume Next
    m = Application.Match(ActiveCell.Address(0, 0), TabOrderArray, False)
    On Error GoTo exitsub
    
    'if activecell is not in Taborder array start at first cell
    If IsError(m) Then
        'goto first cell in array
        i = LBound(TabOrderArray)
    Else
        'get corresponding array index
        i = m + LBound(TabOrderArray) - 1
        'increment i value based on tab direction
        i = i + IIf(Direction = xlPrevious, -1, xlNext)
        'ensure stay within array bounds
        If i > UBound(TabOrderArray) Then
            i = LBound(TabOrderArray)
        ElseIf i < LBound(TabOrderArray) Then
            i = UBound(TabOrderArray)
        End If
    End If
    'select cell based on array element
    Application.EnableEvents = False
    Range(TabOrderArray(i)).Select
exitsub:
    Application.EnableEvents = True
End Sub


Function TabOrderArray() As Variant
'set the tab order of input cells - change ranges as required
TabOrderArray = Array("C6", "C8", "F14", "G14")


End Function

You will need to make the following changes shown in RED for your project:

TabSheetName – enter the name of your worksheet e.g “Sheet1”

TabOrderArray Function – Enter the Range Addresses in required Tab Order.

Place all following code In the Thisworkbook code page:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If Sh.Name = TabSheet Then SetOnkey xlOn
End Sub


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = TabSheet Then SetOnkey xlOff
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If ActiveSheet.Name = TabSheet Then SetOnkey xlOn
End Sub
 
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
SetOnkey xlOff
End Sub

Hopefully this will do what you want however, I am not around much today but I know Jerry monitors the board & perhaps he will be kind enough to step in should you need any further assistance.

Hope Helpful

Dave

 

[QueenS]

Hi Dave

I'm sorry I missed seeing your TabOrder solution. But this code worked beautifully for what I needed!

Thank you.

 

[dmt32]

Hi Dave

I'm sorry I missed seeing your TabOrder solution. But this code worked beautifully for what I needed!

Thank you.

Hi,
Glad solution does what you want - I will let Jerry know we have another satisfied user.

Many thanks for your feedback, it is very much appreciated.

Dave