Solved: Auto Tab

[maxflia10]

Happy New Year to all.

I would like to know if the following is possible. I have a range, A1:C100. Whenever I enter any single character, in say B1, Excel tabs to C1. When I enter any single character in C1, tab to A2.

TIA

[Charlize]

A little example with the onkey event. Way to much trouble I believe. Perhaps there is a better way.

Works with numbers 1, 2 and 3 of the standard keyboard (not the keys of the keypad but the numbers above the letters).

Charlize

[Bob Phillips]

Problem with this method is you have to setup OnKey for every character you want. Other than this you will need to hit Enter to tell Excel it is done and then you can use worksheet change event to monitor if just one character.

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:C100" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) = 1 Then
Me.Cells(.Row - (.Column Mod 3 = 0), .Column Mod 3 + 1).Select
If Intersect(ActiveCell, Me.Range(WS_RANGE)) Is Nothing Then
Me.Range(WS_RANGE).Cells(1, 1).Select
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click

[maxflia10]

Thank-you very much for the advice and code to both of you!

[JimmyTheHand]

Bob, your code doesn't work well with other ranges. It's the "Mod 3" and the absolute row & column indices.
Here's another approach, which works well even on "F4:H12". (Code frame was borrowed from you, thanks )

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Ix As Long, Ad As String

Set Rng = Range("F4:G50") '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Rng) Is Nothing Then
If Len(Target.Value) = 1 Then
Ad = Target.Address(False, False, xlR1C1, , Rng)
Ix = Val(Mid(Ad, 3)) * Rng.Columns.Count + Val(Mid(Ad, InStr(Ad, "C") + 2)) + 1
Rng((Ix Mod Rng.Cells.Count) + 1).Select
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[Bob Phillips]

Bob, your code doesn't work well with other ranges. It's the "Mod 3" and the absolute row & column indices.
Here's another approach, which works well even on "F4:H12". (Code frame was borrowed from you, thanks )

It was designed for the target range of A1:C100. If you want a different range it has to be adjusted.