Tab into and out of a Combo box

**rich80** · 03-16-2007, 05:16 AM

Hi,

I've got a worksheet which uses comboboxes at certain points for users to input data. At other points in the sheet they are entering directly into cells.

I've got the sheet protected, with the input cells unlocked so that it is possible to jump to them using the tab key.

However, I can't seem to make the tab key jump to any of the comboboxes. Ideally I'd like to set the full order for the tab key so that it jumps between input cells and comboboxes at the required stage. Any ideas?

**Carim** · 03-16-2007, 05:22 AM

Hi,

If I recall correctly, just Insert Name Define say input for all your input cells and Tab will work fine ...

**rich80** · 03-16-2007, 05:31 AM

If I recall correctly, just Insert Name Define say input for all your input cells and Tab will work fine ...

This works for the cells, but I can't make it recognise the comboboxes as part of that.

**Carim** · 03-16-2007, 05:34 AM

Sorry ... just tested it ... to refresh my memory and it works fine ...

P.S. Let's agree on the definition of "combobox" .... a cell which has data validation.....

**davesexcel** · 03-16-2007, 06:05 AM

Hi,

There are no tab properties for controls, you will be required to place a code in the control such as

Please Login or Register  to view this content.

when you press tab in comboBox1 you will go to TextBox1

**rich80** · 03-16-2007, 06:11 AM

Maybe I'm being a bit dim...

I've attached a copy of the workbok that I'm using, stripped so it just has sample data.

you can see that Cells C13:C18 are overlaid with 6 ComboBoxes. These are linked to the cell underneath and allow our users to select a code from a list by entering the first few digits.

At the moment, the cursor will tab from E9 -> C13:D13 (merged cell) -> K13:L13 (merged cell). I need it to jump from E9 -> ComboBbox1 -> K13:L13 etc. However, I don't want it to jump to C13:D13 (the cell underneath ComboBox1, which it links to)

I tried the Insert > Name > Define function, but it wouldn't recognise the formula.

Help...!

**Carim** · 03-16-2007, 06:19 AM

Understand now ...
As I said earlier, your comboboxes are objects which come on top of the spreadsheet ... they are actually in their own layer ...
My recommendation for you is to transform these comboboxes into regular cells with Data Validation ... and you will be fine ...

**davesexcel** · 03-16-2007, 07:49 AM

Originally Posted by rich80

Maybe I'm being a bit dim...

I've attached a copy of the workbok that I'm using, stripped so it just has sample data.

you can see that Cells C13:C18 are overlaid with 6 ComboBoxes. These are linked to the cell underneath and allow our users to select a code from a list by entering the first few digits.

At the moment, the cursor will tab from E9 -> C13:D13 (merged cell) -> K13:L13 (merged cell). I need it to jump from E9 -> ComboBbox1 -> K13:L13 etc. However, I don't want it to jump to C13:D13 (the cell underneath ComboBox1, which it links to)

I tried the Insert > Name > Define function, but it wouldn't recognise the formula.

Help...!

Here's something else you could do,
place the code in the worksheet module

Please Login or Register  to view this content.

adjust to suite your requirements

**rich80** · 03-16-2007, 11:37 AM

Thanks for your reply Dave,

Used this one to get out of the combobox using the tab.

Private Sub ComboBox1_KeyDown(ByVal Keycode As MSForms.ReturnInteger, ByVal shift As Integer)
If Keycode = 9 Then
range("K13:L13").Activate
End If

End Sub

Only problem is, the users won't necessarily be making a change to to the cell before the comboBox - they might just tab through it. This means that the command worksheet_change won't work.

Any ideas on how you could call a macro just on cell address?

Carim - unfortunately I can't use a validation field because the users need to be able to select the list based on input of the first couple of characters - the list is actually 3000 entries long...

**davesexcel** · 03-16-2007, 02:38 PM

Here is another code you could use

Please Login or Register  to view this content.

**rich80** · 03-20-2007, 06:11 AM

Wow, that's fantastic!

Thanks for all your help on this. It's made life so much easier!

Rich

**davesexcel** · 03-20-2007, 06:38 AM

Originally Posted by rich80

Wow, that's fantastic!

Thanks for all your help on this. It's made life so much easier!

Rich

No problem,
As Carim suggested, a dropdown menu from the data validation would be a lot simpler though..

here's how to do that

http://www.contextures.com/xlDataVal01.html

**rich80** · 03-21-2007, 12:53 PM

There are 3000 items in the list. Using a combobox allows people to jump to the relevant section of the list by entering the first couple of characters ,so they can then select the relevant entry.

NB: in this bit of code, which allows you to jump into the combobox -

If Not Intersect(Range("C15"), Target) Is Nothing Then
ActiveSheet.ComboBox3.Activate
Application.SendKeys "%{down}"
End If

it's better if you remove the Application.SendKeys "%{down}". Otherwise, although the dropdown list appears by default, the first character that you enter always errors (not sure what it's called, but it's the little square).

**davesexcel** · 03-21-2007, 02:12 PM

Originally Posted by rich80

There are 3000 items in the list. Using a combobox allows people to jump to the relevant section of the list by entering the first couple of characters ,so they can then select the relevant entry.

NB: in this bit of code, which allows you to jump into the combobox -

If Not Intersect(Range("C15"), Target) Is Nothing Then
ActiveSheet.ComboBox3.Activate
Application.SendKeys "%{down}"
End If

it's better if you remove the Application.SendKeys "%{down}". Otherwise, although the dropdown list appears by default, the first character that you enter always errors (not sure what it's called, but it's the little square).

hI RICH,
With the selection change, as soon as C15 is selected then the event is activated which then activates combobox3, with your worksheet this only works by tabbing or using the arrow key because the ComboBox is in front of the cell.

Sure remove the

Please Login or Register  to view this content.

But at least you know about it now

**zines** · 10-31-2013, 04:11 AM

Hello,

I know this forum is old, but this is exactly what I am looking for. However, the problem is, as soon as I start typing within the combo box it jumps out of the combo box. I am not ready for it to leave the combo box. For example, if I type “W” it fills the first item in the list that starts with W and jumps out of the box. Well, I want it to start filling in the combo box as I am typing, which it does this by default, but I don’t want it jumping out of the box right away. I might want the second or third item that starts with “W” and not necessarily the first one. I do want to allow for typing and not be forced to select from the dropdown every single time.

In case someone is questioning this, I do have script to make sure that a valid item is typed in as well and not something made up, if it isn’t in the list it will display a message and send the user back to the combo box. This information was beside the point, but I figured someone might ask.

I guess I really need to be able to use the tab key or down arrow to move out of the box. Thanks for any help someone can provide.

Zines

**Fotis1991** · 10-31-2013, 04:30 AM

My suggestion is to start a new thread for this even that you say that you have the same problem as,

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

**zines** · 10-31-2013, 06:16 AM

I figured it out. Don't use this as noted previously in this forum:

Please Login or Register  to view this content.

Use what I have below instead. I have my combo box called cboSites instead of ComboBox1. The KeyCode = 9 is the number for the tab key. You can put whatever key you want by changing the 9 value. You can also make additional if statement if you want to use the enter key or something else.

Please Login or Register  to view this content.

I hope this will help someone, but it works perfect. I couldn't have done it without davesexcel getting me to think in the right direction.

Thanks,

Zines

**mitchinater** · 03-14-2017, 10:51 PM

Originally Posted by davesexcel

Hi,

There are no tab properties for controls, you will be required to place a code in the control such as

Please Login or Register  to view this content.

when you press tab in comboBox1 you will go to TextBox1

Hello, I am new to this forum, but your answers on this question were very helpful. I have an additional question. I am attempting to leave a ComboBox, and would like to send the focus to a form control button. I don't want the button to be clicked automatically, but would like the user to be able to press the "enter" key once the focus is on the button and then have the macro run. Any thoughts??

**protonLeah** · 03-15-2017, 12:50 AM

mitchinater,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.