Shevlin,
If they are control toolbox controls, this code will work for them. This is
based upon 3 controls, and the one you determine to be previous or next is
defined up-front.
Dim fBackwards As Boolean
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Combobox3"
Const ctlNext As String = "ComboBox2"
Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Dim fBackwards As Boolean
Const ctlPrev As String = "Combobox1"
Const ctlNext As String = "ComboBox3"
Select Case KeyCode
Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp
Application.ScreenUpdating = False
'Determine forwards or backwards
fBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp)
If Application.Version < 9 Then ActiveSheet.Range("A1").Select
If fBackwards Then
ActiveSheet.OLEObjects(ctlPrev).Activate
Else
ActiveSheet.OLEObjects(ctlNext).Activate
End If
Application.ScreenUpdating = True
End Select
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Shevlin Ryan" wrote in message
...
I have an Excel 97 worksheet with a large number of combo boxes for data
selection. Thanks to this group, they are working great!
It would be nice, though, if I could move from box to box, or box to
spreadsheet cell, by pressing Enter or Tab, like I can in an Access
form. I don't see any way to do this in the Properties or Format
windows. Is there a way to do it with VB? Or do I need to upgrade to a
newer version?
Thanks in advance.