Preventing ComboBox from Activating when Not in Use

  • Dear All Hi,


    Hope all of you are safe from the Covid19 Pandemic.


    Mean while, I have been struggling for hours to try and solve this issue. It's not consistent, but the combobox get's activated very now and then when I am doing something else.


    I tried going through several threads in here but couldn't figure-out the solution as I am not any good at VBA.


    I got the sheet done with the help of below thread and I realized reading some of the threads here that the issue is something to do with "ListFillRange", but got no clue to sort it by myself.


    https://www.extendoffice.com/d…rop-down-list-search.html


    Any help would be highly appreciated.


    Attached is my File.


    Thanks.

  • Don't use ListFillRange. Change your code to this:


    Code
    1. Private Sub ComboBox1_GotFocus()
    2.     With ComboBox1
    3.         .ListFillRange = vbNullString
    4.         .List = Application.Range("DropDownList01").Value
    5.         .DropDown
    6.     End With
    7. End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • @ Rory


    I did Clear the ListFillRange from the Properties and pasted your code below the respective Sheet and I am running towards an error when I debug it highlights the line No. 4


    :(

  • It works in your sample workbook for me. What error are you getting?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • @ Rory


    Yeah... Same issue... Neither does it pick anything from the Drop down Narrowing it down, stays same for a while and then the error pops-up.

  • Curious. What version of Excel are you using?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Ah, I think I see the problem. Try this code:


    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi Guys,


    Sorry for the Late Reply.


    Had some stuff I had to take care of which didn't allow me to look into this matter.


    rory


    This time it somewhat gives a solution but not without a new catch.


    When I start to type the Drop-Down opens up but doesn't get update then and there as I type in.

    But let's say I got an agent name partially right,

    If I stop typing it ( While the Drop-Down remains the same ) click another cell and try to continue type again,

    Then the Drop-Down opens up with the updated set of Data.


    Why is that? :/:|:(