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.

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.

Wow, Bob, that was fast!

I pasted the code as you said. It works fine when going from the first
box to the second, but I get an error when going from the second to the
third. "Runtime error 1004. Unable to get the OLEObjects property of
the Worksheet class". Clicking Debug shows
"ActiveSheet.OLEObjects(ctlNext).Activate" highlighted in the "Private
Sub ComboBox2_KeyDown..." section.

It's probably something simple, but I have other things pressing right
now. I'll look at it later.

Thanks a million!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

http://groups.google.com/groups?thre...GP11.phx .gbl

Here is some code posted by Rob Bovey that might give you some ideas:

Message 2 in thread
From: Rob Bovey )
Subject: Navigating via tabbing on a worksheet



Newsgroups: microsoft.public.excel.programming
Date: 2003-06-06 14:40:38 PST


The TabOrder property is an inherited property. That means it comes from
the container that a control is situated in. A UserForm supplies this
property, a worksheet doesn't. You can still tab amongst controls on a
worksheet, you just have to code it yourself using each control's KeyDown
event procedure.

In the sample event procedure below I'll assume a hypothetical situation
where we have three textboxes: TextBoxPrevious, TextBoxCurrent, and
TextBoxNext. This event procedure shows you how to use VBA to emulate
tabbing behavior. Pressing Tab moves from TextBoxCurrent to TextBoxNext and
pressing Shift+Tab moves from TextBoxCurrent to TextBoxPrevious. The Up and
Down arrow keys and the Enter key are given similar behavior.

Private Sub TextBoxCurrent_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 we must select a cell before activating another
control.
Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then TextBoxPrevious.Activate Else _
TextBoxNext.Activate
Application.ScreenUpdating = True
End Select
End Sub

--
Regards,
Tom Ogilvy


"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.

Got it! It was simple, I just needed time to look. My worksheet has no
control named ComboBox3, it jumps to ComboBox17 (I went through a lot of
learning). Changing the 3's to 17's fixed it. Thanks again.

Shevlin Ryan wrote:
Wow, Bob, that was fast!

I pasted the code as you said. It works fine when going from the first
box to the second, but I get an error when going from the second to the
third. "Runtime error 1004. Unable to get the OLEObjects property of
the Worksheet class". Clicking Debug shows
"ActiveSheet.OLEObjects(ctlNext).Activate" highlighted in the "Private
Sub ComboBox2_KeyDown..." section.

It's probably something simple, but I have other things pressing right
now. I'll look at it later.

Thanks a million!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks, Tom. This is very similar to what Bob Phillips posted, but
different enough to spark some questions. I'll see what I can dig up
for answers. If I can't find any, I'll be back.

Tom Ogilvy wrote:

http://groups.google.com/groups?thre...GP11.phx .gbl

Here is some code posted by Rob Bovey that might give you some ideas:

Message 2 in thread
From: Rob Bovey )
Subject: Navigating via tabbing on a worksheet



Newsgroups: microsoft.public.excel.programming
Date: 2003-06-06 14:40:38 PST


The TabOrder property is an inherited property. That means it comes from
the container that a control is situated in. A UserForm supplies this
property, a worksheet doesn't. You can still tab amongst controls on a
worksheet, you just have to code it yourself using each control's KeyDown
event procedure.

In the sample event procedure below I'll assume a hypothetical situation
where we have three textboxes: TextBoxPrevious, TextBoxCurrent, and
TextBoxNext. This event procedure shows you how to use VBA to emulate
tabbing behavior. Pressing Tab moves from TextBoxCurrent to TextBoxNext and
pressing Shift+Tab moves from TextBoxCurrent to TextBoxPrevious. The Up and
Down arrow keys and the Enter key are given similar behavior.

Private Sub TextBoxCurrent_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 we must select a cell before activating another
control.
Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s) pressed.
If bBackwards Then TextBoxPrevious.Activate Else _
TextBoxNext.Activate
Application.ScreenUpdating = True
End Select
End Sub

I suspect Bob's post was inspired by it although Bob's post hadn't appeared
at the time I posted.

--
Regards,
Tom Ogilvy

"Shevlin Ryan" wrote in message
...
Thanks, Tom. This is very similar to what Bob Phillips posted, but
different enough to spark some questions. I'll see what I can dig up
for answers. If I can't find any, I'll be back.

Tom Ogilvy wrote:


http://groups.google.com/groups?thre...GP11.phx .gbl

Here is some code posted by Rob Bovey that might give you some ideas:

Message 2 in thread
From: Rob Bovey )
Subject: Navigating via tabbing on a worksheet



Newsgroups: microsoft.public.excel.programming
Date: 2003-06-06 14:40:38 PST


The TabOrder property is an inherited property. That means it comes from
the container that a control is situated in. A UserForm supplies this
property, a worksheet doesn't. You can still tab amongst controls on a
worksheet, you just have to code it yourself using each control's
KeyDown
event procedure.

In the sample event procedure below I'll assume a hypothetical
situation
where we have three textboxes: TextBoxPrevious, TextBoxCurrent, and
TextBoxNext. This event procedure shows you how to use VBA to emulate
tabbing behavior. Pressing Tab moves from TextBoxCurrent to TextBoxNext
and
pressing Shift+Tab moves from TextBoxCurrent to TextBoxPrevious. The Up
and
Down arrow keys and the Enter key are given similar behavior.

Private Sub TextBoxCurrent_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 we must select a cell before activating
another
control.
Sheet1.Range("A1").Select
''' Activate the appropriate control based on key(s)
pressed.
If bBackwards Then TextBoxPrevious.Activate Else _
TextBoxNext.Activate
Application.ScreenUpdating = True
End Select
End Sub

Shevlin,

It was fast as I keep a library of useful code, so all I hade to do was copy
and paste (and test a bit)<vbg

--

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
...
Wow, Bob, that was fast!

I pasted the code as you said. It works fine when going from the first
box to the second, but I get an error when going from the second to the
third. "Runtime error 1004. Unable to get the OLEObjects property of
the Worksheet class". Clicking Debug shows
"ActiveSheet.OLEObjects(ctlNext).Activate" highlighted in the "Private
Sub ComboBox2_KeyDown..." section.

It's probably something simple, but I have other things pressing right
now. I'll look at it later.

Thanks a million!



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!