Change TAB direction using VBA

PPriest

New Member
Joined
Jun 11, 2018
Messages
36
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?

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:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

PPriest

New Member
Joined
Jun 11, 2018
Messages
36
I was able to get the option to work perfectly when pressing the Enter key, but still unable to figure out for the Tab key.

Option Explicit

Private Sub Worksheet_Activate()
If Range("H2").Value = "Row" Then
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturnDirection = xlDown
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,595
Messages
5,625,697
Members
416,128
Latest member
WarJamAnd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top