If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problems with macro, setting "tab order" of unprotected cells
I found this macro online for controlling tab order among unlocked
cells in a protected worksheet, and modified it to list the cells in the tab order I need them, per instructions for the macro: 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("B1", "B3", "B4", "B5", "F1", "F3", "B10", "B12", "E14", "B16", "F38", "A43", "A44", "A45", "A46", "A47", "A48", "A49", "A50", "A51", "A52", "A53", "A54", "A55", "A56", "A57", "A58", "A59", "A60", "F76", "F78") '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 ---- I have two problems. The first is that this doesn't work reliably. Sometimes after I unprotect and edit the sheet, protect it again and test it, it will skip some cells entirely... and the cells it skips can be different from one test to the next (but always skips the same set of cells until after the next edit). The other problem is that I need the macro to work whether or not a change has been made in any of the cells, which could be different each time the worksheet is used. This version doesn't do that, and I am not trained in VBA. I used to be a programmer and can tell what the macro is doing as I read through it, but have no idea what code I would need to get it to do what I want. |
#2
|
|||
|
|||
Problems with macro, setting "tab order" of unprotected cells
Apologies... I just realized I left out a bit of important detail. I'm
using Excel 2003 on Win XP Pro SP3. |
#3
|
|||
|
|||
Problems with macro, setting "tab order" of unprotected cells
Alright, maybe I can help, I am doing the same thing, the problem with
this code I have is that it cycles through all the referenced cells before it repeats. THis has two drawbacks, First you have to tell it exactly every cell to go to and secondly, if you make a mistake, you have to tab through every cell before you can correct. Trying to find a fix for this currently. I got the code from here, http://www.ozgrid.com/forum/showthread.php?t=82272 This is what the code I use looks like, and it does work, with the limitations listed above. Dim aTabOrd As Variant Dim iTab As Long Dim nTab As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iNew As Long If IsEmpty(aTabOrd) Then aTabOrd = Array("F6", "F7", "U7", "F8", "F9", "F10", "F11", "F12", "A14", "E14", "J14", _ "O14", "A16", "D16", "A18", "E18", "A21", "L21", "A24", "F24", "J24", "N24", "C25", "H25", _ "U25", "I26", "A29", "C29", "A31", "H31", "M31", "B33", "H33", "Z33", "AG33", "E35", "H35", _ "K35", "N35", "Z35", "AG35", "C36", "F36", "Z36", "AG36", "A38", "G38", "J38", "Z38", "AG38", _ "C39", "AG39", "H40", "AG40", "F41", "O41", "AG41", "A43", "G43", "AG43", "B45", "E45", "I45", _ "AG45", "B47", "E47", "H47", "K47", "AG47", "E50", "I50", "U50", "AG50", "E51", "AG51", "G53", _ "I53", "N53", "B55", "E55", "H55", "N55", "U55", "Z55", "G57", "I57", "N57", "U57", "Z57", "AE57", _ "D59", "U59", "E62", "H62", "N62", "E63", "I63", "M63", "E64", "I64", "M64", "Q64", "U64", _ "E65", "I65", "E67", "I67", "G69", "Z69", "AD69", "AI69", "Z71", "AD71", "AI71", "Z72", "AD72", _ "AI72", "A73", "R73", "Z73", "AD73", "AI73", "A74", "R74", "Z74", "AD74", "AI74", "Z75", "AD75", _ "AI75", "G78", "AC2") nTab = UBound(aTabOrd) + 1 iTab = 0 Else On Error Resume Next iNew = WorksheetFunction.Match(Target.Address(False, False), aTabOrd, 0) - 1 If Err Then iTab = (iTab + 1) Mod nTab Else iTab = iNew End If On Error GoTo 0 End If Application.EnableEvents = False Range(aTabOrd(iTab)).Select Application.EnableEvents = True End Sub |
Thread Tools | |
Display Modes | |
|
|