VBA Code for custom Tab Order in Excel

Question

How can I create a custom tab order that is not contingent on a protected worksheet.  I have found the following code on-line but I am having some issues with the code.  For one it only advances via tab if I enter a change into a given cell.  Another problem is if I enter data inside a cell that is not listed in the code it gives an error message.  Any suggestions.

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

Answer 1

I posted the question in regard to VBA code for a custom tab order in Mr. Excel.  I received 2 very detailed answers, which I will have to take some time to see if I can get them to work in my program.  For those following this thread the link to their answers can be found at: http://www.mrexcel.com/forum/excel-questions/739188-visual-basic-applications-code-tab-order.html#post3633721

Answer 2

Your code does not seem to be Mac specific. You might want to post your question in the **Mr. Excel**forum for additional suggestions.

Answer 3

I didn't get an error code.  In fact the code given seems to work, ( although I am having some problems with cells duplicating after running the code).  My main problem is that the code only works to advance a cell if information is entered into the cell.    I have too many unprotected cells to use sheet protection to accomplish my task.  My ultimate goal is to have a custom tab order without using sheet protection; for example A1, F3, C8, etc...

Answer 4

You state what you don't want (i.e., "only advances...changes in a given cell"), so in addition to knowing what error you're getting, it would be helpful to know what behavior you're expecting.

Do you want those 6 cells to be the ONLY cells accessible? If not, what do you want to happen? If so, what should happen when the user clicks on a different cell?

Answer 5

What is the error message when you debug the code?