I have set the tab order for my worksheet from some code I found in another post. It works perfectly for going forward, but if you want to go backwards there is an error. Can someone please look at my code and tell me where my problem lies

Private Sub ComboBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer
Dim bBackwards As Boolea
Select Case KeyCod
''' These are the only keys we care about
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyU
Application.ScreenUpdating = Fals
''' Determine if we need to move backwards
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp
''' Activate the appropriate control based on key(s) pressed
If bBackwards Then Range("L10").Activate Els
ComboBox1.Activat
Application.ScreenUpdating = Tru
End Selec
End Su

Ryan,

Through sheer messing around I made it work for me - and also made it stop
crashing Excel(!). I changed the If-Then statement and now it seems to
work.

By the way, did you notice my last follow-up to our earlier thread, where I
found how to show the list when you tab into the ComboBox? I added it below
as well, so ComboBox1 drops down when tabbed to.

Private Sub ComboBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
Range("L10").Activate
Else
With ComboBox1
.Activate
.DropDown
End With
End If
Application.ScreenUpdating = True
End Select
End Sub

hth,

Doug

"Ryan" wrote in message
...
I have set the tab order for my worksheet from some code I found in
another post. It works perfectly for going forward, but if you want to go
backwards there is an error. Can someone please look at my code and tell me
where my problem lies?

Private Sub ComboBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then Range("L10").Activate Else
ComboBox1.Activate
Application.ScreenUpdating = True
End Select
End Sub

I also found that with this code, so I fixed. Here is an example for 3
combos


Private Sub Combobox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean

Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' In Excel 97 must select cell before activating another
control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
ComboBox3.Activate
Else
ComboBox2.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub Combobox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' In Excel 97 must select cell before activating another
control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
ComboBox1.Activate
Else
ComboBox3.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub

Private Sub Combobox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' In Excel 97 must select cell before activating another
control.
If Application.Version < 9 Then Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
ComboBox2.Activate
Else
ComboBox1.Activate
End If
Application.ScreenUpdating = True
End Select
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Doug Glancy" wrote in message
...
Ryan,

Through sheer messing around I made it work for me - and also made it stop
crashing Excel(!). I changed the If-Then statement and now it seems to
work.

By the way, did you notice my last follow-up to our earlier thread, where
I
found how to show the list when you tab into the ComboBox? I added it
below
as well, so ComboBox1 drops down when tabbed to.

Private Sub ComboBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal
Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then
Range("L10").Activate
Else
With ComboBox1
.Activate
.DropDown
End With
End If
Application.ScreenUpdating = True
End Select
End Sub

hth,

Doug

"Ryan" wrote in message
...
I have set the tab order for my worksheet from some code I found in
another post. It works perfectly for going forward, but if you want to go
backwards there is an error. Can someone please look at my code and tell
me
where my problem lies?

Private Sub ComboBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger,
ByVal Shift As Integer)
Dim bBackwards As Boolean
Select Case KeyCode
''' These are the only keys we care about.
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
''' Determine if we need to move backwards.
bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
''' Activate the appropriate control based on key(s)
pressed.
If bBackwards Then Range("L10").Activate Else
ComboBox1.Activate
Application.ScreenUpdating = True
End Select
End Sub

Doug

That code fix was excellent!!! It works perfectly. Thank you so much for helping me. You have made my Monday

Ryan