A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problems with macro, setting "tab order" of unprotected cells



 
 
Thread Tools Display Modes
  #1  
Old March 23rd, 2009, 09:11 PM posted to microsoft.public.excel.misc
Foxcole
external usenet poster
 
Posts: 10
Default 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  
Old March 24th, 2009, 02:55 AM posted to microsoft.public.excel.misc
Foxcole
external usenet poster
 
Posts: 10
Default 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  
Old May 14th, 2009, 09:08 PM posted to microsoft.public.excel.misc
Cyrus[_2_]
external usenet poster
 
Posts: 1
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.