Insert tabs in to an excel worksheet

[Melinda]

I would like to know how to insert tabs into a excel worksheet. I would like to be able to tab to column D to enter some information then column X to enter information. But then I would like to go to column AB to enter some information then tab backwards to column P to enter some more information. How do I set up these tabs? I have tried to protect the sheet but it doesn't seem to work.

Thanks in advance for the help.

Also keep up the good work. I have learned so many interesting things from this forum.

Melinda

[TheAntiGates]

Consider temporarily setting the intervening column widths to 0. I wouldn't bother with code, if you're sufficiently disciplined to change it back when you're done. Also note in XL03 and prior tools/options/edit gives some control over active cell navigation following hitting the enter key. However note that in some circumstances Excel "as a convenience" does the equivalent of a "carriage return" - e.g.

A1 B1
A2 B2

After typing into B1 and hitting enter it goes to A2 in SOME circumstances. This isn't VBA so someone PM me if they locate the "rules" for that behavior. I suspect that contiguous nonformulas trigger it.

AAR, that "convenience" might be beneficial to you, if you do trigger it.

Note, you could also redesign, though you've probably already considered that. That would mean to set col. D to read from col. A of sheet Input, X from Input's B, etc., letting you arrange the input on sheet Input to most painless entry.

[mdmackillop]

You could also create a small navigation userfom with Option Buttons to switch between columns. I use such to work with large (15+ sheet) workbooks.

[Melinda]

The sheet has about 8000 rows with 31 columns. So it is a good size worksheet. This is the only worksheet in the file.

[Bob Phillips]

This might help you

http://xldynamic.com/source/xld.xlFAQ0008.html

[TheAntiGates]

Here is an event-driven solution. Try it, play with it. To do this kind of thing with a macro can frustrate you later, so consider using this on a temporary basis. I.e., once the data is substantially filled, and you want to do other navigation on the sheet, consider renaming the routine Worksheet_SelectionChange to xWorksheet_SelectionChange, effectively disabling it. Or set up a button that toggles Worksheet_SelectionChange functionality to immediately exit, for when the tabbing functionality is not desired.

As you try this you will note another "frustration" in that its mechanism is whenever you land in column E it jumps to column X, even if you didn't just enter a value in column D. That is because it drives off Worksheet_SelectionChange rather than Worksheet_Change. You may prefer it the other way, causing it only to tab when you enter data.

So, as is, use the tab or enter key after entering data in column D, which puts you in column E, and it jumps to column X. Anytime you go to column E, it will go to column X. Try it, play with it

'ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
  Call initializeNavTable
End Sub

'Sheet1 code(or whatever it is)
Option Explicit
Dim dong As Long

Private Sub Worksheet_Activate()
  dong = Application.MoveAfterReturnDirection
  Application.MoveAfterReturnDirection = xlToRight
End Sub
Private Sub Worksheet_Deactivate()
  Application.MoveAfterReturnDirection = dong
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Integer
  For i = 0 To UBound(lNavCols, 1)
    If Target.Column = lNavCols(i, 0) Then
      Selection.Offset(0, lNavCols(i, 1)).Select: exit sub
    End If
  Next i
End Sub

'Module1
Option Explicit
Dim NavTable As Variant
Public lNavCols() As Long

Sub initializeNavTable()
  Dim i As Integer
  NavTable = Array("D", "X", _
                   "X", "AB", _
                   "AB", "P")
  ReDim lNavCols((UBound(NavTable)) \ 2, 2)
  For i = 0 To UBound(lNavCols, 1)
    lNavCols(i, 0) = Range(NavTable(2 * i) & "1").Column + 1
    lNavCols(i, 1) = Range(NavTable(2 * i + 1) & "1").Column - lNavCols(i, 0)
  Next i
End Sub

But xldennis' (Gord's) solution in the link in the post above looks way mondo cooler. I'd go that way first.

[Aussiebear]

G'day Melinda, Welcome to VBAX, In the attached workbook I have Sheet protected withthe exception of 3 columns. Excel will open at the first non protected column then move to the next etc. This could work for you but only if you go to these columns to enter or alter data.

Password is "Password". Is this the sort of thing you are chasing?

[mdmackillop]

Another option
In ThisWorkbook module
[vba]Private Sub Workbook_Open()
Application.OnKey "{tab}", "MyTab"
End Sub
[/vba]

In a standard module: Change Case and col numbers to suit

[vba]Sub MyTab()
Select Case ActiveCell.Column
Case 2
col = 6
Case 4
col = 8
Case 10
col = 3
Case Else
col = ActiveCell.Column + 1
End Select
Cells(ActiveCell.Row, col).Select
End Sub
[/vba]