Need Help Coding ComboBoxes

**Beast_OnThe_Keys** · 02-18-2019, 06:47 PM

To whom it may concern,

I am currently attempting to create an order form which allows the user to select a product using Combo-box's. I like this because they can begin to type the name of the product and have it auto populate, rather than searching a drop down menu for ages. I am also using a VLOOKUP based on what is selected in the Combo-Box to generate the item number. My issue right now is I cannot easily navigate between combo-boxes using enter or tab. Please understand this is my first day using developer tools so I am fairly behind the eight ball. The boxes function just like they should ( I have 21 of them in cells B14:B34 ). When I use the combo-box in cell B14 and begin typing my product name, it populates it perfect, however instead of just being able to hit enter to move to the next combo-box in B15, I have to use my mouse to click into the box to make it work correctly. I have used some different codes I found online. Some of them allowed me to hit enter to go to the next combo-box, however, the autofill would stop working. Does anyone out there have a solution?

Your help is greatly appreciated

**kev_** · 02-19-2019, 03:56 AM

Welcome to the forum

Tabbing to pre-specified combobox can be set in combobox's properties when on UserForm but not when placed directly on a worksheet

It would have been helpful to tell us what you had tried and had not worked with your existing setup
Are you using Form Control or active-x combo-boxes?
If suggestion below does not work for you, please provide a bit more information - thanks

Note - this suggestion applies to active-x combo boxes

ComboBox2.Activate is the line of code to activate ComboBox2

1. Clicking on any cell after making selection in ComboBox1, takes user to ComboBox2

Private Sub ComboBox1_LostFocus()
    ComboBox2.Activate
End Sub

2. Or try the same line of code as the LAST line in ComboBox1_Change

**Beast_OnThe_Keys** · 02-19-2019, 11:26 AM

Good Morning Kev_ & thank you for the welcome!

It is an active-x combo box. I have used the following code in attempt to make this work;

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode = 13 Or KeyCode = 9 Then
Range("B15").Activate
End If
End Sub

Is there a way I can attach the file to the forum so you could take a look for yourself. I am so close, and this sheet is going to make my work so much easier. I am going to try the code above right now.

EDIT: I used the code above and it worked as described. As soon as you click off combo-box1 the focus is shifted to combo-box2. As this is better than the alternative, I still would like to be able to use the enter key. The document I am creating will be used for our salesforce as a method of submitting orders. I fear that if i dont make it as simple as possible they will just refuse to use it and continue submitting orders via email. With this document it will be easy to see who ordered what and when rather than searching thousands of emails. I never worked with sales directly before, but they are a special group of people who I feel only care about the final result of the sale, and not the 15 other steps it takes to get product out the door!

I appreciate your help very much, if you have further insights, or a good way to train yourself in coding I am all ears.

**Beast_OnThe_Keys** · 02-19-2019, 11:58 AM

Kev_,

Couldn't you use a similar line of coding, but force the enter/tab to lose focus making it shift to combobox-2? Kinda like a mouse click?

**kev_** · 02-19-2019, 12:21 PM

instead of just being able to hit enter to move to the next combo-box in B15

Why are you trying to activate a range

If KeyCode = 13 Or KeyCode = 9 Then
  Range("B15").Activate
End If

Have you tried activating the combobox itself (replace with correct combobox name)

If KeyCode = 13 Or KeyCode = 9 Then
  ComboBox2.Activate
End If

Are the comboboxes linked to the cells where they lie?
Could try this code ...

If KeyCode = 13 Or KeyCode = 9 Then
    For Each objX In ActiveSheet.OLEObjects
        If TypeName(objX.Object) = "ComboBox" Then
            If objX.LinkedCell = "B15" Then objX.Activate
        End If
    Next
End If

**Beast_OnThe_Keys** · 02-19-2019, 01:02 PM

Hey Kev,

Yes they are linked to the cells below them, and I was trying to activate a range because I have no clue what I am doing. However, I have managed to make this work by using the tab key with the following code throughout each combobox;

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox1.Index), Shift
End If

End Sub

Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox2.Index), Shift
End If

End Sub

Private Sub ComboBox3_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox3.Index), Shift
End If

End Sub

Private Sub ComboBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox4.Index), Shift
End If

End Sub

Private Sub ComboBox5_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox5.Index), Shift
End If

End Sub

Private Sub ComboBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox6.Index), Shift
End If

End Sub

Private Sub ComboBox7_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox7.Index), Shift
End If

End Sub

Private Sub ComboBox8_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox8.Index), Shift
End If

End Sub

Private Sub ComboBox9_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox9.Index), Shift
End If

End Sub

Private Sub ComboBox10_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox10.Index), Shift
End If

End Sub

Private Sub ComboBox11_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox11.Index), Shift
End If

End Sub

Private Sub ComboBox12_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox12.Index), Shift
End If

End Sub

Private Sub ComboBox13_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox13.Index), Shift
End If

End Sub

Private Sub ComboBox14_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox14.Index), Shift
End If

End Sub

Private Sub ComboBox15_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox15.Index), Shift
End If

End Sub

Private Sub ComboBox16_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox16.Index), Shift
End If

End Sub

Private Sub ComboBox17_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox17.Index), Shift
End If

End Sub

Private Sub ComboBox18_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox18.Index), Shift
End If

End Sub

Private Sub ComboBox19_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox19.Index), Shift
End If

End Sub

Private Sub ComboBox20_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox20.Index), Shift
End If

End Sub

Private Sub ComboBox21_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If KeyCode = vbKeyTab Then
NavOle Me.OLEObjects(Me.ComboBox21.Index), Shift
End If

End Sub

Private Sub NavOle(ByRef OleActive As OLEObject, Shift As Integer)

If Shift = 1 Then
If OleActive.Index = 1 Then
Me.OLEObjects(Me.OLEObjects.Count).Activate
Else
Me.OLEObjects(OleActive.Index - 1).Activate
End If
Else
If OleActive.Index = Me.OLEObjects.Count Then
Me.OLEObjects(1).Activate
Else
Me.OLEObjects(OleActive.Index + 1).Activate
End If
End If

End Sub

**torachan** · 02-19-2019, 02:34 PM

Hi.
Just an aged gents view.
You would be better familiarising yourself with 'userforms' they make coding a whole lot easier.
Also when used on the 'sheet' ActiveX also tends to be unpredictably if you intend placing the app on various other computers.
torachan.

Need Help Coding ComboBoxes

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need Help Coding ComboBoxes

Re: Need Help Coding ComboBoxes

Re: Need Help Coding ComboBoxes

Re: Need Help Coding ComboBoxes

Re: Need Help Coding ComboBoxes

Re: Need Help Coding ComboBoxes

Re: Need Help Coding ComboBoxes

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] In need of help regarding combo box coding and button coding (Access form project)

Coding comboboxes to limit to list

Populating comboboxes based on selection of values from another comboboxes & capturing val

Help! Entire Row Hidden - w/Two ComboBoxes - Doesn't Hide the ComboBoxes

filtering comboboxes depend on value of other comboboxes

[SOLVED] Vb macro coding for transpose - complete coding?

[SOLVED] Calculating Time in Userform Textbox and Filtering Comboboxes Coding Help

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions