Tabbing from Cell to ComboBox

I am trying to create a excel based form (not User Form) which meets some our business accessibility standards (i.e. a user who cannot use a mouse, must be able to complete it using the keyboard).

I have got around the checkbox issue by creating faux checkboxes and asking the users to enter an X if appropriate. However, part of the information that they need to complete is in the form of a drop-down list (the information is being collected by a hidden datasheet for importing into Access and we need to control the format of the information being provided). I originally implemented this by using data validation on a cell and linking to a named range on a hidden sheet (there are over 100 options to select from). This made it easy for the user to tab to the cell with the data validation in it but, because of the length of the options list, it was a bit unwieldy and users were a bit frustrated by not being able to enter the first couple of letters and for the entry to auto-complete (maybe this is possible in a data validation created list but I couldn't seem to implement it). I have now created a combobox in place of the cell (from the Control Toolbox) and linked this to the name range - so far so good and I have autocomplete working. The trouble now is that I can't get the combobox to get focus without using the mouse to click it.

After writing "War and Peace", what I wanted to know is if I can do this by VBA? The trouble is that I am a good magpie and can kind of cut and paste code if I can find it but I am shocking when it comes to writing anything from scratch. My instinct tells me that I want to trigger some kind of code when the user tabs out of the cell before the combobox to activate and dropdown the combobox and then to be able to either tab or hit enter in order to give focus to the next cell in the form.

The sheet and workbook are protected with only the cells requiring completion being unlocked.

Hope someone can help. TIA
Nicki

Hello Nicki,

This macro works with one Control Toolbox TextBox named "TextBox1" on the worksheet. The macro can be expanded to handle more TextBoxes if you need them. If you have any questions let me know.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

Bettatronic - thanks I'll look at this tomorrow when the level of red wine decreases and it makes more sense!

This doesn't seem to do the trick when I incorporate the code into my workbook. Can you implement it on the test file that I posted? I see that it sort of works on the file that you attached.

Thanks (now with a clearer head)

Bettatronic - sorry, the password is "SkimmedMilk"

Thanks
Nicki

What a star, that is perfect, thank you so much

Originally Posted by barefaced66

What a star, that is perfect, thank you so much

I think I spoke too soon. When I put that into the full version of the form it still doesn't work. I don't know whether it is a conflict with the other code in the full form, so am attaching an "anonymised" version of it to see whether you can get it work?

Once again, thanks for your time and effort.
N

test.zip

Originally Posted by bettatronic

Now I hope it'll work pretty fine for you Nicki.

Have fun !

It works brilliantly, thank you so much again for your time