Tabbing through Protected Sheet

[CONFUSED AT WORK]

Just windering if there is a way to tab through a locked sheet. I have many
unlocked cells, that i need access to, but the problem is I need them in a
specific order, for example, right now its,

a1 - tab > b1 tab >c1

Where i Need it a1 tab > c3 tab > b2

Didnt know if there was a way or not.


THanks.

 

[Tom Hutchins]

Here is one way...

On another sheet (could be hidden), create a table of your from/to cell
addresses. For example, I put the following in A1:B6 of Sheet2:

A3 C3
C3 B3
B3 A15
A15 C15
C15 B15
B15 A3

In a VBA module, add the following macro:

Sub NextCell()
Dim CurrAddr As String, NewAddr
Dim LkupRng As Range
On Error GoTo NCerr
CurrAddr = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Set LkupRng = Sheets("Sheet2").Range("A:B")
NewAddr = Application.WorksheetFunction.VLookup(CurrAddr, LkupRng, 2, False)
If IsError(NewAddr) Then Exit Sub
If ActiveSheet.Range(NewAddr).Locked = True Then Exit Sub
ActiveSheet.Range(NewAddr).Activate
Set LkupRng = Nothing
Exit Sub
NCerr:
MsgBox Err.Description, vbExclamation, "NextCell"
End Sub

You can assign a keystroke combination to the macro or attach it to a button
on the worksheet or a toolbar.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[Gord Dibben]

You could use a named range.

See Bob Phillips' site for instructions.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

Or use event code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "B5", "C3", "A10", "C10", "A4")

'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


Gord Dibben MS Excel MVP