VBA Drop Down List

Copper Contributor
Hi Everyone,
I developed a combo box drop down list and added a code that works fine but doesn’t let me scroll down my listed options using my down arrow keyboard button. Is there a code to use the down arrow and enter button on the drop down list.

Here is the code I’m currently using:

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownListv1"
Me.ComboBox1.DropDown
End Sub

Hope anyone can help me!
15 Replies
I expect changing the listfillrange causes the listindex to be reset, hence you cannot go through the items iwith the keyboard as the list keeps changing?
I’m not changing the list range. The issue is when I click on the combo box and try to scroll down it crashes.
Well, yes, the code that runs as you change the combo updates the listfillrange
Ok I understand what you mean. Yes the purpose of my combo box is for me to type in the first two letters of a name and it gives me options to choose the right one. Like doing a google search in excel for names.

What would be an alternate code?
Can you post an anonymized version of your file?

@Anggie 

You can set the ListFillRange utilizing an event called GotFocus so that it doesn't set the ListFillRange each time you type in the ComboBox.

 

Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = "DropDownListv1"
End Sub

 

This works but it only allows me to select the first name on my list and not let me keep scrolling down the other options using the down key. Unless I use my clicker to select the other names.
What do you suggest can be added?

@Anggie 

Are you having any other events like KeyPress or KeyDown etc?

If I insert a dummy ComboBox and have the Change and GotFocus events, I can use my up and down arrow keys to scroll up and down.

Why not upload the file in question after removing the sensitive data if any?

Hi, can you explain how the dummy box works? I have typed in another code:

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

If KeyCode <> 40 And KeyCode <> 38 Then

ComboBox1.ListFillRange = "DropDownListv1"

Me.ComboBox1.DropDown

Else

KeyCode = 0

End If

End Sub

@Anggie 

That code is the culprit.

Basically with that code, you are asking the code to check, if UP arrow key (KeyCode-38) or Down arrow key (KeyCode-40) is pressed, don't do anything and disable those keys when you say KeyCode = 0 in the Else clause.

 

What's the need of that code? Why are you having same code with multiple events?

Delete this KeyDown event code and your ComboBox will start working.

Well the reason I added the keypress event was because I want the list to update while I type in the combo box. The problem with that is when I use the up and down arrow keys to scroll down the list it crashes and I would lose the initial codes I used. That is why I added the two keypress events.

@Anggie 

 

I am not sure what exactly you are trying to do.

All I can suggest is, disable all the codes for ComboBox1 and have only one code which should be as below to set the ListFillRange once the ComboBox gets focus.

 

Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = "DropDownListv1"
End Sub

 

And then see if your ComboBox starts behaving normal and then think of adding other codes for ComboBox events if required.

The code you sent me runs smoothly. Now I want to know which element in my combo box has the focus. And how can I change that selection or move that focus to another element.

@Anggie 

Now place this code...

Private Sub ComboBox1_Change()
Me.ComboBox1.DropDown
End Sub

And use UP and Down arrow keys to scroll up and down and hit Enter to select an item.

@Subodh_Tiwari_sktneer
Excel gives "Compile error: Invalid use of Me keyword" on this code:

Private Sub ComboBox1_Change()
Me.ComboBox1.DropDown
End Sub