Macro allowing Tab key in ComboBox - requires extra keypress?

**dcunneen** · 06-15-2010, 04:58 AM

Hi all,

Having a small problem with a worksheet. I've added some code (found on this forum I think) to navigate in & out of a combo box using the Tab key. The path is from E5 -> C6 (which has the combo box on top of it) -> C7:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Problem: selection sticks on C7 for an extra keystroke. ie: if pushing TAB each time it goes like this: E5 -> combobox -> C7 -> C7 -> C8. Strangely, it doesn't stick anywhere when using Shift-Tab to go backwards.

Does anyone know why this happens / how to fix it?

Thanks in advance

**romperstomper** · 06-15-2010, 05:27 AM

I can't replicate that behaviour in 2003. Any chance you can post a suitably censored sample showing the problem?

**dcunneen** · 06-16-2010, 12:33 AM

No problem, thanks for your reply. Here is the file with all superfluous elements stripped.

I also noticed that if I tab from E5 -> C6 -> C7, and then immediately shift-tab, it skips C6 and jumps back to E5? It's like the underlying cell C6 remains selected when the combobox on top of it is activated.

**romperstomper** · 06-16-2010, 06:43 AM

The only thing I can find currently that seems to help is adding a sendkeys line:

Please Login or Register  to view this content.

So that's two of my least favourite things in combination... (sendkeys and activex controls on worksheets!)

**dcunneen** · 06-16-2010, 09:02 PM

Haha, agreed it's not ideal, but hey whatever works...

It's not a 100% solution because now whenever a combobox is selected with the mouse or via a shift-tab, the next tab keystroke skips straight over the adjacent cell. I might have to fiddle around and find the right combination of cell and control activation to prevent that... as if my code isn't ad-hoc enough already!

Cheers for your help, if you think of anything else let me know

**dcunneen** · 06-17-2010, 04:54 AM

After some playing around I've found the solution.

The problem was because the cell underlying the combobox was actually a merged cell range - C6:G6 etc. I still don't quite know why this is a problem, but I fixed it by un-merging the cells and locking D6:G6.

Now the only cell underlying the box is C6, and the tab key moves smoothly

**romperstomper** · 06-17-2010, 05:08 AM

Thanks for posting back with the answer.
As a matter of interest, why are you using combobox controls rather than validation dropdowns?

**dcunneen** · 06-17-2010, 05:31 AM

I couldn't see a way to do what I wanted with validation dropdowns. The user has to be able to select an entry from the list, which automatically populates the corresponding 3-letter code, but the also has to be able to:
* type the first few letters and have it auto-complete
* modify the selected list entry without losing the 3-letter code they have selected, and
* type their own entry if they can't find it in the list.
Since the user has to be able to enter their own 3-letter codes, I couldn't have a formula in those cells either.

For your interest here is the code for one of the change events:

Please Login or Register  to view this content.

I'm currently trying to make some of the boxes get their list from a dynamic named range ie " =OFFSET(Input!$J$3,1,0,COUNTA(Input!$J:$J)-1,1) " but that causes exceptions for some reason I haven't yet figured out.

**romperstomper** · 06-17-2010, 05:41 AM

I'd guess you're referring to a zero row range under certain circumstances with the COUNTA part.
To be honest, from what you describe I'd probably use a userform for this. Either for all the data entry, or as a popup to enter data for specific cells. (I *hate* putting ActiveX controls directly on sheets.)

**dcunneen** · 06-17-2010, 08:49 PM

Agreed, the activex controls kind of appeared & evolved over time which is why they're there, but it would probably be better to design from scratch with userforms.