VBA Script to change tab order for cells

[lord_yo]

Hi everyone

I have a Excel 2003 sheet which is used for various text inputs in unprotected cells (sheet is locked). Since it's a printable form with two columns, the field order is not correct. Now I'm looking for a VBA script to set a custom cell order when using the tab or enter keys. In other words, I want to be able to specify that after cell A1 comes cell C3, then A6, then C12, etc., and back to the beginning. The tab jumps should work even if no text input is made in the fields.

Any ideas?
Thanks! LY

 

[Richard Schollar]

Hi

When using xl2010, if I unlock A1, C3, A6 and C12 (having selected them in this order using Ctrl and select using the mouse) and then protect the sheet (making sure I can only select unlocked cells), the tab key will work by selecting A1, then C3 then A6 then C12. Enter key works differently by selecting A1 then A6 then C3 then C12.

 

[lord_yo]

When using xl2010, if I unlock A1, C3, A6 and C12 (having selected them in this order using Ctrl and select using the mouse) and then protect the sheet (making sure I can only select unlocked cells), the tab key will work by selecting A1, then C3 then A6 then C12. Enter key works differently by selecting A1 then A6 then C3 then C12.

Thanks for the tip. Unfortunately, that doesn't seem to work in Excel 2003.

 

[jim may]

FWIW - Richard's suggestion ALSO works in xl2007

 

[Richard Schollar]

Try selecting the cells in the order you want them to work in and naming the range (eg Insert>Name>Define and call it myRange or whatever), then making them unlocked and protecting the sheet.

 

[lord_yo]

Try selecting the cells in the order you want them to work in and naming the range (eg Insert>Name>Define and call it myRange or whatever), then making them unlocked and protecting the sheet.

Nope, unfortunately, that doesn't work either.

I think I really need a script checking keystroke activity and cycling through it. I found one script, but this one only works if the cells are changed, not if you just hop from one cell to the next:

Private Sub Worksheet_Change(ByVal Target As Range) 
     
    Dim aTabOrd As Variant 
    Dim i As Long 
     
     'Set the tab order of input cells
    aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10") 
     
     '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 
            Else 
                 'Select next cell in the array
                Me.Range(aTabOrd(i + 1)).Select 
            End If 
        End If 
    Next i 
     
End Sub

 

[Richard Schollar]

I just booted into my XP OS which runs xl2003 and what I outlined in post #2 works in 2003 for me. Have you tried it on a completely clean blank workbook and followed the steps and seeing if it then works? It was the tab key that was leaping from A1 to C3 then to A6 then C12 for me (and NOT the enter key).

 

[lord_yo]

I just booted into my XP OS which runs xl2003 and what I outlined in post #2 works in 2003 for me. Have you tried it on a completely clean blank workbook and followed the steps and seeing if it then works? It was the tab key that was leaping from A1 to C3 then to A6 then C12 for me (and NOT the enter key).

Sorry, my bad - the example I gave which was answered in post #2 (A1, C3, A6, C12) is a bad one, because that one relies on the Excel's normal tab order. So yes, that one works for me as well, but others do not.

Consider this example:

A1 -> A2 -> A3 -> B1/B2 (merged) -> B3

This example works with Enter, but not with Tab. And worse, the cursor gets stuck in a perpetual loop jumping from A1 to B1/B2 and back.

 

[Richard Schollar]

See if you can work with this - it needs to go into the code module of the worksheet that is protected. The variable vRngs needs to hold all the cell addresses that you want to specify the order of in the order you want them selected. Please ensure all these cells are unlocked and can be selected on the protected sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vRngs As Variant
Dim vMarker As Long
vRngs = VBA.Array("A1", "A2", "A3", "B1", "B2", "B3")
If Target.Count = 1 Then
    vMarker = Application.Match(Target.Address(0, 0), vRngs, 0)
    If IsNumeric(vMarker) Then
        If vMarker = UBound(vRngs) + 1 Then
            Range(vRngs(0)).Select
        Else
            Range(vRngs(vMarker)).Select
        End If
    End If
End If
End Sub

 

[lord_yo]

Thanks! This one works, at least when a cell is changed. The KeyPress event (which I considered as alternative to Worksheet_Change) unfortunately doesn't work for Tab and Return, so I'll have to live with that.

Appreciate all your help!
LY