I want to pull down a combo box that is within an Excel sheet and select an
item within the list by the keyboard instead of using the mouse which will
satisfy the user in case the user doesn't want to switch between keyboard
and mouse all the time. How can I activate the combobox events (such as
dropdown and select an item by pressing return key) automatically from the
keyboard strokes or do I have to write my own procedures ?

Oscar

Hi Oscar,

Have you tried ALT + down arrow?

Oscar wrote:

I want to pull down a combo box that is within an Excel sheet and select an
item within the list by the keyboard instead of using the mouse which will
satisfy the user in case the user doesn't want to switch between keyboard
and mouse all the time. How can I activate the combobox events (such as
dropdown and select an item by pressing return key) automatically from the
keyboard strokes or do I have to write my own procedures ?

Oscar



--

Cheers
Andy

http://www.andypope.info

What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet and select
an
item within the list by the keyboard instead of using the mouse which will
satisfy the user in case the user doesn't want to switch between keyboard
and mouse all the time. How can I activate the combobox events (such as
dropdown and select an item by pressing return key) automatically from the
keyboard strokes or do I have to write my own procedures ?

Oscar

Yes,
that didn't work.



"Andy Pope" schreef in bericht
...
Hi Oscar,

Have you tried ALT + down arrow?

Oscar wrote:

I want to pull down a combo box that is within an Excel sheet and select
an
item within the list by the keyboard instead of using the mouse which
will
satisfy the user in case the user doesn't want to switch between
keyboard
and mouse all the time. How can I activate the combobox events (such as
dropdown and select an item by pressing return key) automatically from
the
keyboard strokes or do I have to write my own procedures ?

Oscar



--

Cheers
Andy

http://www.andypope.info

Hi Oscar,

It worked for me on both data validation and a control toolbox combo.

Maybe as Tom suggests you can tell which combo you are using.

Oscar wrote:

Yes,
that didn't work.



"Andy Pope" schreef in bericht
...

Hi Oscar,

Have you tried ALT + down arrow?

Oscar wrote:


I want to pull down a combo box that is within an Excel sheet and select

an

item within the list by the keyboard instead of using the mouse which

will

satisfy the user in case the user doesn't want to switch between

keyboard

and mouse all the time. How can I activate the combobox events (such as
dropdown and select an item by pressing return key) automatically from

the

keyboard strokes or do I have to write my own procedures ?

Oscar



--

Cheers
Andy

http://www.andypope.info





--

Cheers
Andy

http://www.andypope.info

Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the mouse

press on 'edit modus' at the Visual Basic bar to edit the properties of the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet and select
an
item within the list by the keyboard instead of using the mouse which
will
satisfy the user in case the user doesn't want to switch between
keyboard
and mouse all the time. How can I activate the combobox events (such as
dropdown and select an item by pressing return key) automatically from
the
keyboard strokes or do I have to write my own procedures ?

Oscar

I put this macro in a general module (in the VBE, Insert=Module)

Sub ActivateCombobo()
ActiveSheet.ComboBox1.Activate
End Sub

Then went back to Excel and in Tools=Macro=Macros, I selected it and
clicked the option button and but a lowercase b in for the short cut key, so
the short cut key is Ctrl+b


Then I could do Ctrl+B, Alt and down arrow do drop the list, (or use just
the down arrow to go throught the list without droping the list), then used
down and up arrow to scroll through the list and enter to make a selection.

--
Regards,
Tom Ogilvy


Oscar wrote in message
.nl...
Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the mouse

press on 'edit modus' at the Visual Basic bar to edit the properties of
the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet and
select
an
item within the list by the keyboard instead of using the mouse which
will
satisfy the user in case the user doesn't want to switch between
keyboard
and mouse all the time. How can I activate the combobox events (such
as
dropdown and select an item by pressing return key) automatically from
the
keyboard strokes or do I have to write my own procedures ?

Oscar

Thanks Tom,

the keyword to focus the combobox was 'activate' as you mentioned. However,
there is one problem,

When I push the arrow down button, the ComboBox_click() event is fired
immediately which calls the necessary processing routine. So I am not able
to scroll down the list to look for the specific item and then press at
RETURN. Is there a way to overcome this ?

regards,
Oscar





"Tom Ogilvy" schreef in bericht
...
I put this macro in a general module (in the VBE, Insert=Module)

Sub ActivateCombobo()
ActiveSheet.ComboBox1.Activate
End Sub

Then went back to Excel and in Tools=Macro=Macros, I selected it and
clicked the option button and but a lowercase b in for the short cut key,
so
the short cut key is Ctrl+b


Then I could do Ctrl+B, Alt and down arrow do drop the list, (or use just
the down arrow to go throught the list without droping the list), then
used
down and up arrow to scroll through the list and enter to make a
selection.

--
Regards,
Tom Ogilvy


Oscar wrote in message
.nl...
Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the mouse

press on 'edit modus' at the Visual Basic bar to edit the properties of
the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet and
select
an
item within the list by the keyboard instead of using the mouse
which
will
satisfy the user in case the user doesn't want to switch between
keyboard
and mouse all the time. How can I activate the combobox events (such
as
dropdown and select an item by pressing return key) automatically
from
the
keyboard strokes or do I have to write my own procedures ?

Oscar

Lightly tested, but you could probably use the Keydown or Keyup event to
trigger your action - test for the enter key. Move your code out of the
click event.

Another consideration is how you get out of the Combobox. You need to do
that as well and the Keyup event can be used as well:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Then
' your click event code
ActiveCell.Activate
End If

End Sub


--
Regards,
Tom Ogilvy



Oscar wrote in message
.nl...
Thanks Tom,

the keyword to focus the combobox was 'activate' as you mentioned.
However,
there is one problem,

When I push the arrow down button, the ComboBox_click() event is fired
immediately which calls the necessary processing routine. So I am not able
to scroll down the list to look for the specific item and then press at
RETURN. Is there a way to overcome this ?

regards,
Oscar





"Tom Ogilvy" schreef in bericht
...
I put this macro in a general module (in the VBE, Insert=Module)

Sub ActivateCombobo()
ActiveSheet.ComboBox1.Activate
End Sub

Then went back to Excel and in Tools=Macro=Macros, I selected it and
clicked the option button and but a lowercase b in for the short cut
key,
so
the short cut key is Ctrl+b


Then I could do Ctrl+B, Alt and down arrow do drop the list, (or use
just
the down arrow to go throught the list without droping the list), then
used
down and up arrow to scroll through the list and enter to make a
selection.

--
Regards,
Tom Ogilvy


Oscar wrote in message
.nl...
Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the mouse

press on 'edit modus' at the Visual Basic bar to edit the properties
of
the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet and
select
an
item within the list by the keyboard instead of using the mouse
which
will
satisfy the user in case the user doesn't want to switch between
keyboard
and mouse all the time. How can I activate the combobox events
(such
as
dropdown and select an item by pressing return key) automatically
from
the
keyboard strokes or do I have to write my own procedures ?

Oscar

Tom,

I've also implemented the KeyDown routine as well and I am able to handle
the processing now.
There is only one small but main problem : I need to set the
combobox.visible =false after the processing.
While this has worked within the Combobox_Click() event, implementing
ComboBox1.visible =false within the ComboBox1_KeyDown event, causes Excel to
crash. Any suggestion how to overcome this ?

Oscar


"Tom Ogilvy" schreef in bericht
...
Lightly tested, but you could probably use the Keydown or Keyup event to
trigger your action - test for the enter key. Move your code out of the
click event.

Another consideration is how you get out of the Combobox. You need to do
that as well and the Keyup event can be used as well:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Then
' your click event code
ActiveCell.Activate
End If

End Sub


--
Regards,
Tom Ogilvy



Oscar wrote in message
.nl...
Thanks Tom,

the keyword to focus the combobox was 'activate' as you mentioned.
However,
there is one problem,

When I push the arrow down button, the ComboBox_click() event is fired
immediately which calls the necessary processing routine. So I am not
able
to scroll down the list to look for the specific item and then press at
RETURN. Is there a way to overcome this ?

regards,
Oscar





"Tom Ogilvy" schreef in bericht
...
I put this macro in a general module (in the VBE, Insert=Module)

Sub ActivateCombobo()
ActiveSheet.ComboBox1.Activate
End Sub

Then went back to Excel and in Tools=Macro=Macros, I selected it and
clicked the option button and but a lowercase b in for the short cut
key,
so
the short cut key is Ctrl+b


Then I could do Ctrl+B, Alt and down arrow do drop the list, (or use
just
the down arrow to go throught the list without droping the list), then
used
down and up arrow to scroll through the list and enter to make a
selection.

--
Regards,
Tom Ogilvy


Oscar wrote in message
.nl...
Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the mouse

press on 'edit modus' at the Visual Basic bar to edit the properties
of
the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet
and
select
an
item within the list by the keyboard instead of using the mouse
which
will
satisfy the user in case the user doesn't want to switch between
keyboard
and mouse all the time. How can I activate the combobox events
(such
as
dropdown and select an item by pressing return key)
automatically
from
the
keyboard strokes or do I have to write my own procedures ?

Oscar

In the keydown event

Application.Ontime Now, "Hidecombo"

in a general/standard module

Public Sub Hidecombo()
Activesheet.Combobox1.visible = False
End sub

--
Regards,
Tom Ogilvy


Oscar wrote in message
. nl...
Tom,

I've also implemented the KeyDown routine as well and I am able to handle
the processing now.
There is only one small but main problem : I need to set the
combobox.visible =false after the processing.
While this has worked within the Combobox_Click() event, implementing
ComboBox1.visible =false within the ComboBox1_KeyDown event, causes Excel
to
crash. Any suggestion how to overcome this ?

Oscar


"Tom Ogilvy" schreef in bericht
...
Lightly tested, but you could probably use the Keydown or Keyup event to
trigger your action - test for the enter key. Move your code out of the
click event.

Another consideration is how you get out of the Combobox. You need to
do
that as well and the Keyup event can be used as well:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByVal
Shift As Integer)
If KeyCode = 13 Then
' your click event code
ActiveCell.Activate
End If

End Sub


--
Regards,
Tom Ogilvy



Oscar wrote in message
.nl...
Thanks Tom,

the keyword to focus the combobox was 'activate' as you mentioned.
However,
there is one problem,

When I push the arrow down button, the ComboBox_click() event is fired
immediately which calls the necessary processing routine. So I am not
able
to scroll down the list to look for the specific item and then press
at
RETURN. Is there a way to overcome this ?

regards,
Oscar





"Tom Ogilvy" schreef in bericht
...
I put this macro in a general module (in the VBE, Insert=Module)

Sub ActivateCombobo()
ActiveSheet.ComboBox1.Activate
End Sub

Then went back to Excel and in Tools=Macro=Macros, I selected it
and
clicked the option button and but a lowercase b in for the short cut
key,
so
the short cut key is Ctrl+b


Then I could do Ctrl+B, Alt and down arrow do drop the list, (or use
just
the down arrow to go throught the list without droping the list),
then
used
down and up arrow to scroll through the list and enter to make a
selection.

--
Regards,
Tom Ogilvy


Oscar wrote in message
.nl...
Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the mouse

press on 'edit modus' at the Visual Basic bar to edit the
properties
of
the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel sheet
and
select
an
item within the list by the keyboard instead of using the
mouse
which
will
satisfy the user in case the user doesn't want to switch
between
keyboard
and mouse all the time. How can I activate the combobox events
(such
as
dropdown and select an item by pressing return key)
automatically
from
the
keyboard strokes or do I have to write my own procedures ?

Oscar

Tom,

thanks again for your help, the problem is almost completely solved because
there are still situations sometimes at which Excel crashes again. I've seen
that adding 'Doevents' sometimes can solve the problem. So I will have to
study this first.


regards,
Oscar


"Tom Ogilvy" schreef in bericht
...
In the keydown event

Application.Ontime Now, "Hidecombo"

in a general/standard module

Public Sub Hidecombo()
Activesheet.Combobox1.visible = False
End sub

--
Regards,
Tom Ogilvy


Oscar wrote in message
. nl...
Tom,

I've also implemented the KeyDown routine as well and I am able to
handle
the processing now.
There is only one small but main problem : I need to set the
combobox.visible =false after the processing.
While this has worked within the Combobox_Click() event, implementing
ComboBox1.visible =false within the ComboBox1_KeyDown event, causes
Excel
to
crash. Any suggestion how to overcome this ?

Oscar


"Tom Ogilvy" schreef in bericht
...
Lightly tested, but you could probably use the Keydown or Keyup event
to
trigger your action - test for the enter key. Move your code out of
the
click event.

Another consideration is how you get out of the Combobox. You need to
do
that as well and the Keyup event can be used as well:

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger,
ByVal
Shift As Integer)
If KeyCode = 13 Then
' your click event code
ActiveCell.Activate
End If

End Sub


--
Regards,
Tom Ogilvy



Oscar wrote in message
.nl...
Thanks Tom,

the keyword to focus the combobox was 'activate' as you mentioned.
However,
there is one problem,

When I push the arrow down button, the ComboBox_click() event is
fired
immediately which calls the necessary processing routine. So I am
not
able
to scroll down the list to look for the specific item and then press
at
RETURN. Is there a way to overcome this ?

regards,
Oscar





"Tom Ogilvy" schreef in bericht
...
I put this macro in a general module (in the VBE, Insert=Module)

Sub ActivateCombobo()
ActiveSheet.ComboBox1.Activate
End Sub

Then went back to Excel and in Tools=Macro=Macros, I selected it
and
clicked the option button and but a lowercase b in for the short
cut
key,
so
the short cut key is Ctrl+b


Then I could do Ctrl+B, Alt and down arrow do drop the list, (or
use
just
the down arrow to go throught the list without droping the list),
then
used
down and up arrow to scroll through the list and enter to make a
selection.

--
Regards,
Tom Ogilvy


Oscar wrote in message
.nl...
Tom,

the combobox was added by

choose bar 'Visual Basic'
select the control combobox with entry ability from the bar
draw the combobox at the sheet by making the sqaure with the
mouse

press on 'edit modus' at the Visual Basic bar to edit the
properties
of
the
combobox
the name of the bar is given 'ComboBox1' automatically.

Oscar






"Tom Ogilvy" schreef in bericht
...
What kind of comobox

Data validation (List option)
Dropdown box from the forms toolbar
Combobox from the control toolbox toolbar

--
Regards,
Tom Ogilvy

Oscar wrote in message
. nl...
I want to pull down a combo box that is within an Excel
sheet
and
select
an
item within the list by the keyboard instead of using the
mouse
which
will
satisfy the user in case the user doesn't want to switch
between
keyboard
and mouse all the time. How can I activate the combobox
events
(such
as
dropdown and select an item by pressing return key)
automatically
from
the
keyboard strokes or do I have to write my own procedures ?

Oscar