Get user input value of Excel ComboBox VBA

Question

I have a combobox in Excel which is populated with values from a DB. There are 2 columns with the 2nd column bound and linked to a cell.

I want users to be able to input their own value.

If a user selects an item from the list, everything works great. I get the value in the linked cell.

However, if the user inputs their own data i get a #N/A value in the linked cell.

Is there any way where I can retrieve the value inputted by the user?

Thanks in advance

There will be a _change event on the combo box. Or you can monitor the keypress event for an enter. — MatthewD, Aug 26 '15 at 14:54
@MatthewD I can use the_change or _keypress event, but I dont know how to get the actual value of what the user has inputted. The combobox.value just returns the latest selected item from the list. @ Dragon Samu its just for practical purposes that I have 2 coloumns. The 1st column holds a text and the 2nd column holds a DB id (hidden from the user). However, that should not change my problem of getting the value of the user input. — user2218856, Aug 26 '15 at 17:22
Look at the ComboBox2.Text vs the ComboBox2.Value There is a difference. Not sure if it will return what you are looking for but take a look. http://stackoverflow.com/questions/2844193/distinction-between-using-text-and-value-in-vba-access — MatthewD, Aug 26 '15 at 17:26
I just tested both .value and .text at a different computer, and both worked perfectly. Could that have something to do with the versions of excel? Anyway, thanks a lot for the feedback. It was driving me crazy :) — user2218856, Aug 26 '15 at 17:38
@user2218856 the 2nd column is bound and linked to a cell by settings or by code? — DragonSamu, Aug 27 '15 at 09:21
@MatthewD The .text solution is perfect. It gets me the user input. Thank you so much! — user2218856, Sep 01 '15 at 08:11

score 0 · Answer 1 · edited May 23 '17 at 10:29

Strange that the text or value. You will have to access them after update. But you could watch what is being entered and keep track of it that way.

Declare a global variable to hold what is entered.

Private strComboEntry As String

Then watch what is being keyed in.

Private Sub ComboBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim strKey As String
    strKey = Chr(KeyAscii)
    strComboEntry = strComboEntry & strKey
End Sub

We will have to handle the backspace and delete too.

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

    If KeyCode = 8 Then
        strComboEntry = Left(strComboEntry, Len(strComboEntry) - 1)
        Exit Sub
    End If

End Sub

EDIT: OP says this comment got him the answer.
Look at the ComboBox2.Text vs the ComboBox2.Value There is a difference. Not sure if it will return what you are looking for but take a look. Distinction between using .text and .value in VBA Access

Get user input value of Excel ComboBox VBA

1 Answers1