tab to certain cells

[wibbers2000]

Is there a way in Excel that will only allow certain cells to have data entered via a tab key?

I know how to proect the cells so that data cant be entered into it; but let say I wanted to enter data into cells B4, C4 B7, F12. I can protect al other cells so that just these cells will allow data.

What I would like to di is to start on B4 by default and then when the TAB key is pressed, jump to C4 then B7, then F12 in that sequence and miss all other cells out.

regards
Wibbers

[Bob Phillips]

Is there a way in Excel that will only allow certain cells to have data entered via a tab key?

I know how to proect the cells so that data cant be entered into it; but let say I wanted to enter data into cells B4, C4 B7, F12. I can protect al other cells so that just these cells will allow data.

What I would like to di is to start on B4 by default and then when the TAB key is pressed, jump to C4 then B7, then F12 in that sequence and miss all other cells out.

regards
Wibbers

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

[mdmackillop]

Hi wibbers,
This was an old KB I was working on, but never quite completed. Paste the code into the worksheet module, select any of the cells listed in the code and press Tab. This will loop you around the listed cells. Let me know how you get on.
Regards
MD

[VBA]
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
DoTab target.Offset(0, -1), True
End Sub

Sub DoTab(ByVal target As Range, Optional Test1 As Boolean)
Dim aTabOrd As Variant
Dim i As Long, Test2 As Boolean
'Test1 and Test2 are set to false later to allow the code to run once only
If Test1 = False And Test2 = False Then
Application.EnableEvents = True
Exit Sub
End If
Test1 = False
'Set the tab order of input cells
aTabOrd = Array("B3", "B5", "B7", "B9", "F9", "B11", "F11", "B13", "F13", _
"B15", "B17", "B19", "B21", "B23", "B26", "E26", "B27", "E27", "B29", "E29", _
"B30", "E30", "B31", "E31", "B34", "C43", "B36", "B38", "B40", "B42")
'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
'Just to show what's happening
Selection.Interior.ColorIndex = 6
Test2 = True
Else
'Prevent selection change from running when selection is changed
Application.EnableEvents = False
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
Selection.Interior.ColorIndex = 6
'Setting Test2 to False forces exit from sub when it runs again
'from the penultimate line
Test2 = False
'For debbugging and avoidance of CRI in convoluted code.
'MsgBox Selection.Address(0, 0)
Exit For
End If
End If
Next i
'Running the code again resets the Application.EnableEvents
'to true before exiting the sub
DoTab target
End Sub

[/VBA]