Hello. Looking for help using VBA to change the direction the tab key moves to the next cell.
The goal in mind: I have a workbook with multiple users and each user has their preferred method for data entry. Some prefer entering straight down a column before going to the next column while others prefer entering data across a row of columns.
In cell "H2", I have given the person using the workbook the ability to select either "Row" or "Column". I want the direction the next cell is selected to change based upon what is selected.
* Row -> to the right
* Column -> down
I have figured out how to do this with the enter key, but want the tab key to follow suit. Any ideas?
The goal in mind: I have a workbook with multiple users and each user has their preferred method for data entry. Some prefer entering straight down a column before going to the next column while others prefer entering data across a row of columns.
In cell "H2", I have given the person using the workbook the ability to select either "Row" or "Column". I want the direction the next cell is selected to change based upon what is selected.
* Row -> to the right
* Column -> down
I have figured out how to do this with the enter key, but want the tab key to follow suit. Any ideas?
VBA Code:
Option Explicit
Private Sub Worksheet_Activate()
If Range("H2").Value = "Row" Then
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturnDirection = xlDown
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("H2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Call Worksheet_Activate
End If
End Sub
Last edited by a moderator: