Taborder code trouble

R

RobAbel

New Member
Joined
Jul 6, 2005
Messages
21
Private Sub Worksheet_Change(ByVal Target As Range)
'Rob Abel
'050315 0319
Dim taborder As Variant
Dim i As Long
Application.EnableEvents = False
taborder = Array("d3", "I3", "v3", "l5", "v5", "e7", "n7", "t7", "i9", "q9", "w9", _
"c10", "o10", "c16", "i16", "s17", "e19", "d21", "c23", "i23", "k23", "q19", "p21", "o23", "u23", "w23", "d25", "l25", "s25", "e27", "k27", "o27", "t27", "d31", "j31", "n31", "t31", "f33", "f37", "j37", "m37", "f39", "j39", "m39", "f41", "j41", "q36", "q38", "n40", "s40", "q44")
i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(Target.Address(0, 0), taborder, 0) - 1
On Error GoTo 0
If i = -1 Then GoTo skip
If i < UBound(taborder) Then Range(taborder(i + 1)).Select Else Range(taborder(0)).Select
skip:
Application.EnableEvents = True
End Sub


Help!

I am using the above code to navigate through a form in ms excel 03 for a sales department. Problem is that if you wish to skip one of these cells you can’t just use the TAB key. You have to hit spacebar followed by the tab key. Also the "SHIFT+TAB" function for reversing within the tab order is not working. Any ideas?

Thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Try this:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldCell As Range
    Dim taborder As Variant
    Dim i As Integer
    If OldCell Is Nothing Then
        Set OldCell = Range("D3")
    End If
    taborder = Array("d3", "I3", "v3", "l5", "v5", "e7", "n7", "t7", "i9", "q9", "w9", _
"c10", "o10", "c16", "i16", "s17", "e19", "d21", "c23", "i23", "k23", "q19", "p21", "o23", "u23", "w23", "d25", "l25", "s25", "e27", "k27", "o27", "t27", "d31", "j31", "n31", "t31", "f33", "f37", "j37", "m37", "f39", "j39", "m39", "f41", "j41", "q36", "q38", "n40", "s40", "q44")
    Application.EnableEvents = False
    i = WorksheetFunction.Match(OldCell.Address(0, 0), taborder, False)
    If i > UBound(taborder) Then
        Range(taborder(0)).Select
    Else
        Range(taborder(i)).Select
    End If
    Set OldCell = ActiveCell
    Application.EnableEvents = True
End Sub

The Tab key (or any other cursor movement key) will move to the next cell.

You might want to select the first cell when the workbook is opened:

Code: 
Private Sub Workbook_Open()
    Application.EnableEvents = False
    Worksheets("Sheet1").Activate
    With ActiveSheet
        Application.Goto .Range("A1"), True
        .Range("D3").Select
    End With
    Application.EnableEvents = True
End Sub
 
Upvote 0
Works great!

Just one problem. Now if a user wants to click on a cell to fill it in it automatically advances to the next cell in the taborder. Any way to fix that?

Thanks!
 
Upvote 0
Try:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldCell As Range
    Dim taborder As Variant
    Dim i As Integer
    If OldCell Is Nothing Then
        Set OldCell = Range("D3")
    End If
    taborder = Array("d3", "I3", "v3", "l5", "v5", "e7", "n7", "t7", "i9", "q9", "w9", _
"c10", "o10", "c16", "i16", "s17", "e19", "d21", "c23", "i23", "k23", "q19", "p21", "o23", "u23", "w23", "d25", "l25", "s25", "e27", "k27", "o27", "t27", "d31", "j31", "n31", "t31", "f33", "f37", "j37", "m37", "f39", "j39", "m39", "f41", "j41", "q36", "q38", "n40", "s40", "q44")
    Application.EnableEvents = False
    On Error Resume Next
    i = WorksheetFunction.Match(Target.Address(0, 0), taborder, False)
    If Err = 0 Then
        Application.EnableEvents = True
        Exit Sub
    Else
        Err.Clear
    End If
    On Error GoTo 0
    i = WorksheetFunction.Match(OldCell.Address(0, 0), taborder, False)
    If i > UBound(taborder) Then
        Range(taborder(0)).Select
    Else
        Range(taborder(i)).Select
    End If
    Set OldCell = ActiveCell
    Application.EnableEvents = True
End Sub

If the user clicks in a valid cell it stays selected. Moving away selects the next cell based on the previously selected cell.
 
Upvote 0
Hey,

I copied and pasted the new code you created and the symptoms are still the same. If I click on any of the unlocked cells it automatically forwards me to the next cell in taborder.

Any other ideas?
 
Upvote 0
I tested it as follows:

1. I opened the workbook and pressed Tab 5 times so that E7 was the active cell.

2. I clicked in I3, which remained active.

3. I pressed Tab and N7 became the active cell.

Are you sure you copied the latest code?
 
Upvote 0
I opened a new work book and pasted the new code just as you wrote it. You are correct, if you click on I3 is does stay on I3, but if you click on any other cells of the array it does not stick.

Any thoughts?
 
Upvote 0
I can click on any cell in the array and it sticks.

Please post a step by step list of your actions with what happens and what you would like to happen.
 
Upvote 0
Thanks for your patience with this.

Here is the code that I have:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
Dim taborder As Variant
Dim i As Integer
If OldCell Is Nothing Then
Set OldCell = Range("D3")
End If
taborder = Array("d3", "I3", "v3", "l5", "v5", "e7", "n7", "t7", "i9", "l9", "q9", "w9", _
"c10", "o10", "c16", "i16", "s17", "e19", "d21", "c23", "i23", "k23", "q19", "p21", "o23", "u23", "w23", "d25", "l25", "s25", "e27", "k27", "o27", "t27", "d31", "j31", "n31", "t31", "f33", "f37", "j37", "m37", "f39", "j39", "m39", "f41", "j41", "q36", "q38", "n40", "s40", "q44")
Application.EnableEvents = False
On Error Resume Next
i = WorksheetFunction.Match(Target.Address(0, 0), taborder, False)
If Err = 0 Then
Application.EnableEvents = True
Exit Sub
Else
Err.Clear
End If
On Error GoTo 0
i = WorksheetFunction.Match(OldCell.Address(0, 0), taborder, False)
If i > UBound(taborder) Then
Range(taborder(0)).Select
Else
Range(taborder(i)).Select
End If
Set OldCell = ActiveCell
Application.EnableEvents = True
End Sub

I had an observation. Most of the cells are merged or drop down lists. Do you think that might affect the macro? I3 is not a merged cell and seems to work fine with the macro along with a few other cells later in the array that are not merged.

Again, what I am looking for is the capability to navigate the array using Tab for forward, Shift+Tab for backwards and the ability to click on any of the array cells and fill them in.

Thanks a million.
 
Upvote 0
Personally, I avoid merged cells like the plague, because they cause more problems than they solve. What is the purpose of the merging?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
Copy data from sheet to another sheet with long formula
Replies
5
Views
304
johnnyL
J
K
  • Question
VLOOKUP/Indirect Formula
Replies
9
Views
364
awoohaw
awoohaw
P
  • Solved
Convert value if the date listed within the column is within the month going across the rows
Replies
3
Views
318
Fluff
Fluff
T
  • Solved
Have formula repeat for a value in a cell then use a different formula for a value number of steps
Replies
1
Views
576
Tmoff
T
A
  • Question
VBA Loop to clear contents of fiscal year rows in specific month columns
Replies
6
Views
515
kevin9999
K

Forum statistics

Threads
1,215,752
Messages
6,126,672
Members
449,327
Latest member
John4520

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top