Preventing ComboBox from Activating when Not in Use

nzso · Jul 8th 2020

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.

**rory** · Jul 8th 2020

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

Code

Private Sub ComboBox1_GotFocus()
    With ComboBox1
        .ListFillRange = vbNullString
        .List = Application.Range("DropDownList01").Value
        .DropDown
    End With
End Sub

nzso · Jul 8th 2020

@ 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

**rory** · Jul 8th 2020

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

nzso · Jul 8th 2020

@ Rory

It's a Runtime Error-381

"Could not set the List properly. Invalid Property array Index.

That's what I am getting.

**rory** · Jul 8th 2020

With this file?

nzso · Jul 8th 2020

@ 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.

**rory** · Jul 8th 2020

Curious. What version of Excel are you using?

nzso · Jul 8th 2020

@ Rory

- Professional Plus 2019.

nzso · Jul 9th 2020

@ All,

Any other work around or solutions guys?

**royUK** · Jul 9th 2020

Is this working for you?

**rory** · Jul 9th 2020

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

Code

Private Sub ComboBox1_GotFocus()
    Dim data
    With Application.Range("DropDownList01")
        If .Cells.Count > 1 Then
            data = .Value
        Else
            data = Array(.Value)
        End If
    End With
    With ComboBox1
        .ListFillRange = vbNullString
        .List = data
        .DropDown
    End With
End Sub

Display More

nzso · Jul 23rd 2020

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?

Preventing ComboBox from Activating when Not in Use

Participate now!

Share

Tags