CODEWITHSUNDEEP
excelvba
RalphBiggerton
RalphBiggerton

Reputation: 199

Auto-tabbing between fields in Excel Workbook

The following VBA script for an Excel sheet allows an individual to type text into a cell and then automatically jump to the next cell in the array once they hit enter.

However, this requires someone to type into every cell for the script to advance to the next cell in the array below and does not recognize tab, only enter.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tabArray As Variant
    Dim i As Long
    tabArray = Array("B5", "C6", "D7", "E8")
    Application.ScreenUpdating = False
    For i = LBound(tabArray) To UBound(tabArray)
        If tabArray(i) = Target.Address(0, 0) Then
            If i = UBound(tabArray) Then
                Me.Range(tabArray(LBound(tabArray))).Select
            Else
                Me.Range(tabArray(i + 1)).Select
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

I'd like to let the person hit tab or enter if they want to skip the given field and advance without typing anything in the cell.

Upvotes: 0

Views: 376

Answers (3)

Tim Williams
Tim Williams

Reputation: 166531

Protecting the sheet and unlocking the data-entry cells as suggested by @Basher is probably a better solution IMO, but this sort-of works. Couple of notes:

  1. To "break into" the tab sequence you need to select one of the data-entry cells
  2. To "break out" of the tab sequence (so you can select some other cell maybe) you can make a multi-cell selection.

Code:

Dim lastCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tabArray As Variant
    Dim i As Long, addr, mCurr, indx, mPrev

    If Target.Cells.CountLarge = 1 Then

        tabArray = Array("B5", "C6", "D7", "E8")

        addr = Target.Address(False, False)
        mCurr = Application.Match(addr, tabArray, 0)

        If IsError(mCurr) Then 'current selection isn't a data entry cell
            If lastCellAddress <> "" Then
                'was user previously in a data entry cell?
                mPrev = Application.Match(lastCellAddress, tabArray, 0)
                If Not IsError(mPrev) Then
                    'mPrev is 1-based but tabArray is 0-based...
                    indx = IIf((mPrev - 1) < UBound(tabArray), mPrev, 0)
                    On Error GoTo haveError
                    Application.EnableEvents = False
                    Me.Range(tabArray(indx)).Select '<< select the next entry cell
                    Application.EnableEvents = True
                End If
            End If
        End If
        lastCellAddress = Selection.Address(False, False)
    Else
        lastCellAddress = ""  'breaks out of the sequence
    End If
    Exit Sub

haveError:
    Debug.Print Err.Description
    Application.EnableEvents = True

End Sub

Upvotes: 1

Basher
Basher

Reputation: 331

You can also just protect the sheet and have the input cells unlocked.

Right Click on the Cell, choose "Format cells". Then, go to Protection Tab, and uncheck "Locked".

Once the input cells are unlocked, go to the "Review" Ribbon, and click on Protect Sheet. Uncheck "Select locked cells" and it should only allow you to click on the cells that are unlocked, and can press Tab repeatedly to go to the next available cell.

Upvotes: 3

Michal
Michal

Reputation: 6013

You don't need VBA to achieve this task - it's a built-in Excel functionality.

Simply select your data entry range and after inputting the desired value into the first cell hit TAB, it will take you to the next highlighted cell - either to the right of the current one or to the next row if you were at the end of the row.

Upvotes: 0

Related Questions