thicks
01-30-2012, 08:05 PM
I have a simple excel invoice sheet that I am working on for a friend. He wants the tab order changed while in protected mode; I have unlocked the cells that he wants to fill in. I will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great. I read Anne Troy article on this and downloaded her example, but could not get it to work for me on my sheet.
These are the directions I followed:
On the sheet for which you want to set the tab order, right-click the sheet tab and hit View Code. This opens the Visual Basic Editor (VBE).
Paste the code into the code window that appears at right.
Edit the Array line in the code to suit your tab order.
Hit the Save diskette and close the VBE.
Save and close your Excel workbook.
Here are two of the codes I tried:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrder As Variant
Dim i As Long
Set the tab order of input cells
aTabOrder = Array ("B1?, "B2?, "B3?, "H1?, "H2?, "H3?, "C6?, "G6?, "B7?, "B8?, "B9?, "E9?, "G9?, "A11?, "B11?, "C11?, "I11?, "A12?, "A15?, B15?, "H15?, "A16?, "B16?, "H16?, "A17?, "B17?, "H17?, "A18?, "B18?, "H18?, "A19?, "B19?, "H19?, "A20?, "B20?, "H20?, "A21?, "B21?, "H21?, "A22?, "B22?, "H22?, "A23?, "B23?, "H23?, "A24?, "B24?, "H24?, "I26?, "I28?, "A26?)
Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
If the changed cell is the last array element
If i = UBound(aTabOrder) Then
Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd As Variant
Dim i As Long
'Set the tab order of input cells
aTabOrd = Array("B1", "B2", "B3", "H1", "H2", "H3", C6, G6, B7, B8, B9, E9, G9, A11, B11, C11, I11, A12, A15, B15, H15, A16, B16, H16, A17, B17, H17, A18, B18, H18, A19, B19, H19, A20, B20, H20, A21, B21, H21, A22, B22, H22, A23, B23, H23, A24, B24, H24, I26, I28, A26)
'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 will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great.
Thanks,
Tray
These are the directions I followed:
On the sheet for which you want to set the tab order, right-click the sheet tab and hit View Code. This opens the Visual Basic Editor (VBE).
Paste the code into the code window that appears at right.
Edit the Array line in the code to suit your tab order.
Hit the Save diskette and close the VBE.
Save and close your Excel workbook.
Here are two of the codes I tried:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrder As Variant
Dim i As Long
Set the tab order of input cells
aTabOrder = Array ("B1?, "B2?, "B3?, "H1?, "H2?, "H3?, "C6?, "G6?, "B7?, "B8?, "B9?, "E9?, "G9?, "A11?, "B11?, "C11?, "I11?, "A12?, "A15?, B15?, "H15?, "A16?, "B16?, "H16?, "A17?, "B17?, "H17?, "A18?, "B18?, "H18?, "A19?, "B19?, "H19?, "A20?, "B20?, "H20?, "A21?, "B21?, "H21?, "A22?, "B22?, "H22?, "A23?, "B23?, "H23?, "A24?, "B24?, "H24?, "I26?, "I28?, "A26?)
Loop through the array of cell address
For i = LBound(aTabOrder) To UBound(aTabOrder)
If the changed cell is in the array
If aTabOrder(i) = Target.Address(0, 0) Then
If the changed cell is the last array element
If i = UBound(aTabOrder) Then
Select the first cell in the array
Me.Range(aTabOrder(LBound(aTabOrder))).Select
Else
Select the next cell in the array
Me.Range(aTabOrder(i + 1)).Select
End If
End If
Next i
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd As Variant
Dim i As Long
'Set the tab order of input cells
aTabOrd = Array("B1", "B2", "B3", "H1", "H2", "H3", C6, G6, B7, B8, B9, E9, G9, A11, B11, C11, I11, A12, A15, B15, H15, A16, B16, H16, A17, B17, H17, A18, B18, H18, A19, B19, H19, A20, B20, H20, A21, B21, H21, A22, B22, H22, A23, B23, H23, A24, B24, H24, I26, I28, A26)
'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 will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great.
Thanks,
Tray