On a excel protected worksheet, how do I change the tab order?


G

Guest

I have unlocked certain cells and protected the worksheet, however, I would
like to manipulate the Tab order on the unlocked cells.

Is there a way to do that? (I know I can in Access, but can't figure out
how to in excel)

Please help
 
Ad

Advertisements

P

Paul B

wikgarden, here is one way



Click on each cell in the tab order you would like while
holding down the Ctrl key. Now press Ctrl+F3 and give it
a defined name. Select that name from the namebox and tab
through the cells.

There is a limit of about 25-40 cells you can put into a Named Range due to
a
255 character limit in the Named range.


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
G

Guest

Thank you. I can probably use that in the future, however, there are too
many fields, and this only worked with the first 28 fields. I think I have
around 56 fields that I need to tab through.
 
Ad

Advertisements

P

Paul B

vikgarden, here is some code I found that might help

Using event code like this requires that the user enter something in the
cell. It doesn't matter how the user exits the cell, it will still go to the
next cell. However, if the user doesn't "change" the cell, the macro doesn't
fire, so it's not a true tab order.

Private Sub Worksheet_Change(ByVal Target As Range)

'Tab order

Dim aTabOrder As Variant

Dim i As Long



'Set the tab order of input cells

aTabOrder = Array("A1", "C1", "G3", "B5", "E1", "F4")



'Loop through the array of cell address

For i = LBound(aTabOrder) To UBound(aTabOrder)

'If the changed cell is in the array

If aTabOrder(i) = Target.Address(0, 0) Then

'If the changed cell is the last array element

If i = UBound(aTabOrder) Then

'Select the first cell in the array

Me.Range(aTabOrder(LBound(aTabOrder))).Select

Else

'Select the next cell in the array

Me.Range(aTabOrder(i + 1)).Select

End If

End If

Next i



End Sub



To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium



To change the security settings go to tools, macro, security, security level
and set it to medium


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top