Tab order with VBA

[Henry Pippin]

I'm trying to get the tab order in excel to follow the cell order in the array listed below. I've unlocked those specific cells and protected the worksheet. I added this code by right clicking the worksheet and clicking view code, after typing I save it and then reopened the workbook. But the tab order is still flowing like it would without this code.

Any help would be appreciated.

Henry



Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd As Variant
Dim i As Long
aTabOrd = Array("B3", "C3", "A5", "C5", "A6", "C6", "A7", "C7", "C8", "C9", "C10")
For i = LBound(aTabOrd) To UBound(aTabOrd)
If aTabOrd(i) = Target.Address(0, 0) Then
If i = UBound(aTabOrd) Then
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
End Sub

[Zack Barresse]

Hi there, welcome to the board!

You could use something like this...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd As Variant
Dim i As Long
aTabOrd = Array("B3", "C3", "A5", "C5", "A6", "C6", "A7", "C7", "C8", "C9", "C10")On Error Resume Next
i = WorksheetFunction.Match(Target.Address(0, 0), aTabOrd, 0)
On Error GoTo 0
If i <> 0 Then
If i = UBound(aTabOrd) Then i = LBound(aTabOrd)
Application.EnableEvents = False
Me.Range(aTabOrd(i)).Select
Application.EnableEvents = True
End If
End Sub

[snb]

or


Sub M_snb()
sn=Array("B3", "C3", "A5", "C5", "A6", "C6", "A7", "C7", "C8", "C9", "C10")
For j = 0 To ubound(sn) - 1
Sheets(range(sn(j + 1)).value).Move , Sheets(range(sn(j)).value)
Next
End Sub

[Zack Barresse]

Do they want to move the sheets? Maybe I misread what was asked for. ?

[Henry Pippin]

Do they want to move the sheets? Maybe I misread what was asked for. ?

I tried both suggestions it still doesn't work. I don't want to move sheets, just the tab order on a worksheet. Am I attaching the VBA code to the worksheet correctly?

Henry

[snb]

In that case:


Private Sub Worksheet_Change(ByVal Target As Range)
sn = Split("B3,C3,A5,C5,A6,C6,A7,C7,C8,C9,C10", ",")
If Not IsError(Application.Match(Target.Address(0, 0), sn, 0)) Then Application.Goto Range(sn(Application.Match(Target.Address(0, 0), sn, 0) Mod UBound(sn)))
End Sub

You need to paste the code in the macromodule of the sheet you are working in.

[Zack Barresse]

Since this is a worksheet change event, in the VBE, go into the worksheet code module you want it to work in and paste it there. There can only be one change event per class module (e.g. worksheet or workbook module).

[SamT]

I think the OP is talking about the Tab Key Press event. ie, if current cell = "A5", then on Tab Key Press Select cell "C5"


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim aTabOrd As Variant
Dim i As Long

If Target.Count > 1 Then Exit Sub
aTabOrd = Array("B3", "C3", "A5", "C5", "A6", "C6", "A7", "C7", "C8", "C9", "C10")

For i = LBound(aTabOrd) To UBound(aTabOrd)
If aTabOrd(UBound(aTabOrd)) = Target.Offset(0, -1).Address(0, 0) Then
Range(aTabOrd(LBound(aTabOrd))).Select
Exit For
ElseIf aTabOrd(i) = Target.Offset(0, -1).Address(0, 0) Then
Range(aTabOrd(i + 1)).Select
Exit For
End If
Next i

End Sub

[Zack Barresse]

Ok, that's what I thought and is what my code does w/o a loop.

[SamT]

@ Zack: Elegant.

I had to edit mine, I know it would have failed the first way. :blush

[Zack Barresse]

Why thank ya sir. :)

[Henry Pippin]

Thanks for all the help, but I've tries them all and nothing works.

At a loss!!!!

[SamT]

Henry,

Are you talking about when you press the Tab key on the keyboard or the Name Tabs at the bottom of a worksheet?

What exactly are you trying to do?

[Henry Pippin]

Henry,

Are you talking about when you press the Tab key on the keyboard or the Name Tabs at the bottom of a worksheet?

What exactly are you trying to do?


Move from one cell to another cell on the same spreadsheet, using either tab or enter, but in the order shown in the above code.

Thanks, Henry

[Zack Barresse]

Works for me. Can you post the spreadsheet? What is the code you're using?

[snb]

Of course you have to change something in every cell

[Henry Pippin]

I've attached a screen shot of the spreadsheet and of the code window. The code was posted through right clicking on the Truck Stop Tab and going to view code. You can see from the screen shot what I'm trying to do.

10576

Thanks, the code window is in the next post.

[Henry Pippin]

The code window post.

10577

[snb]

Did you test the attachment I posted ?

This is an Excel Subforum: so I am not interested in pictures. Please post a sample workbook instead.

[Henry Pippin]

When I open the file you sent, it's totally blank.

http://www.vbaexpress.com/forum/attachment.php?attachmentid=10575&d=1378926564

Henry

[SamT]

@ Henry

With snb's file, type something in B3 and press Tab or Enter.

Then view his code and paste it into the code page for the Truck Stop tab.

[snb]

I assumed you wanted to enter something in those cells, so why do you expect them to be filled ?

If you test the attachment maros have to be enabled......