none
How to create this drop down list? RRS feed

  • Question

  • Hi all,

    I've created this spreadsheet with 100 'items' in the drop down list, but I need a drop down list where I can begin typing for a particular thing in the list and it populates it in the field when I press TAB/ENTER.

    I've tried Data Validation drop down but It doesn't auto-fill what's in the drop down list when I type.
    I've also tried ActiveX ComboBox but when it auto-fills, I have to click out of the box to save it. Is there a way I can do this function by simply pressing TAB or ENTER?

    I appreciate any assistance you can provide.

    Friday, November 17, 2017 7:49 AM

All replies

  • Hi all,

    I've created this spreadsheet with 100 'items' in the drop down list, but I need a drop down list where I can begin typing for a particular thing in the list and it populates it in the field when I press TAB/ENTER.



    Are there two drop down lists in your spreadsheet?
    One has 100 items and the other is for typing a particular thing??
    Can you explain what you want to do by using other words? 

    Regards,

    Ashidacchi

    Saturday, November 18, 2017 12:30 AM
  • Hi Lucarious30,

    You had posted a same thread in October. In that thread, Chenchen provide a good idea to use the Combobox.KeyUp event to lose focus of an ActiveX Combobox. I would also suggest you use this event. I would suggest you add some code to check the keycode to check if you pressed TAB or ENTER so it would loss focus once you presses TAB or Enter.

    Here is the example.

    Dim ad As String
    Private Sub ComboBox1_GotFocus()
    ad = ActiveCell.Address
    End Sub
    Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    '9 is TAB's keycode, 13 is ENTER's keycode
    If (KeyCode = 9 Or KeyCode = 13) And ComboBox1.MatchFound Then
    Range(ad).Select
    End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, November 20, 2017 1:57 AM
  • Hi Terry,

    Is there any video or clear guide on how I could action this?

    My knowledge on Excel appears to be seemingly basic when it comes to ActiveX Combobox's

    Kind Regards

    Luke

    Tuesday, November 21, 2017 4:08 PM
  • Hi Luke,

    Please try to refer to below gif.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 22, 2017 8:01 AM