Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tabbing through Protected Sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tabbing through Protected Sheet
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/200...e-elses-macro/ Hope this helps, Hutch "CONFUSED AT WORK" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tabbing through Protected Sheet
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 On Thu, 4 Dec 2008 07:11:02 -0800, CONFUSED AT WORK wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel; tabbing through protected worksheet | Excel Discussion (Misc queries) | |||
Tabbing between protected cells | Excel Worksheet Functions | |||
Tabbing thru User-Edit Cells in Protected Sheet | Excel Discussion (Misc queries) | |||
Tabbing on a Protected worksheet | Excel Worksheet Functions | |||
tabbing in a protected sheet | Excel Discussion (Misc queries) |