VBA Script to change tab order for cells

lord_yo

New Member
Joined
Oct 18, 2006
Messages
39
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

New Member
Joined
Oct 18, 2006
Messages
39
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. :(
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

New Member
Joined
Oct 18, 2006
Messages
39
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:

Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

New Member
Joined
Oct 18, 2006
Messages
39
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.

Code:
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

New Member
Joined
Oct 18, 2006
Messages
39
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,129
Messages
5,599,894
Members
414,343
Latest member
JennyGarcia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top