VBA Excel ComboBox - How do I delete or unbind the list data

I am trying to create a self filtering list in a combobox that sorts itself as the user types. Thanks to my other thread in the forum, I have created an autofilter on the main list in column A (the range is called "TransferringAccountIdentifier") . The filter matches the combobox1.value and then autofilters and copies the shortened list as a range to cell G1.

The combo1.list is filled from the "G1" range  - ComboBox1.List = Sheet3.Range("G1").CurrentRegion.Value

I have also tried a similar method by using rowsource and mapping it to ComboBox1.List = Sheet3.Range("G1").CurrentRegion.Address. This also works fine.

The first major problem is that as the list gets down to one last result only (by either typing enough characters or by clicking on one of the list), it churns out an error.

It seems well documented that .list cannot cope with one item. This is a known problem with combo boxes and the same for rowsource. It has to be an array of 2 or more. So when we get down to one value, we have an error using .list and .rowsource. That means we have to resort to deleting the list and using .additem. (unless anyone can suggest an alternative)

So, no problem. I have trapped the  - Sheet3.Range("G1").CurrentRegion.Count = 1  and if it is only one item, I then attempt to clear the list and just load the single item.

I cannot get the list to clear. How do I unbind the list or rowsource so that the list is cleared???

I have tried combobox1.clear - see the command button
I have tried setting the combobox1.list =""
I have tried setting the combobox1.rowsource =""

I simply cannot get rid of the list.

The second problem is if I enter enough characters (try 'glu'), I get a match and down to a single entry in the filter list. The trap picks up the single entry and moves to the click event but I am unable to force the full vale into the input part of the combobox.

I could probably do it using .additem throughout but as the full list has 90,000 in it, I would rather use list or rowsource.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Some feedback on my help in your last question is appropriate before starting a new one.
Beside Roy_Cox comment that presume deserves an answer, The file that you posted does not contain a combobox that you talked about. Did you post the wrong workbook ?

mikexmlAuthor Commented:
Thanks to Roy and his support with my first query, we got up and running. But comboboxs are not without a series of quirky problems.  For example rowsource or list cannot be one item. Thus a range which can, legitimately in excel, have a count of one, causes havoc to comboboxes in which they are bound.

Tbe combo box is on a form attached to the xlsm. . The problem is that when you detect a list count of one, you need to delete the bound list, clear the combo and use addiem to see the single match.

That is the problem.  I cannot clear the list in combobox1. Thanks.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Martin LissOlder than dirtCommented:
The problem with seemingly not being able to clear the list is due to the way you create the list. IMO the creation of the list should not be in the ComboBox1_Enter event but rather in the Initialize event where it will happen when you load the userform, or in the Activate event where it will happen each time you select the userform. As it is now the ComboBox1.Clear statement does clear the combobox but the process triggers the ComboBox1_Enter event which immediately refills it.

You can see that the combobox is cleared if you put a breakpoint on the End Sub line of the CommandButton1_Click event and when the code gets there, place ?userform1.combobox1.listcount in the Immediate window and press return. For this and more information on debugging please see this article of mine.
Roy CoxGroup Finance ManagerCommented:
The ComboBox is populated the way that Mike requested because he wanted to filter the list displayed. I agree that the Initialize event would be the ideal time to populate.

The repopulation would maybe be better on the exit event or using a TextBox to enter criteria in as I did with my first example in the previous post..
mikexmlAuthor Commented:
It seemed to me that virtually every program & application today that uses a Dropbdown ensures that it has an interal auto filter.  As i recall from my VB6 days there was a property Auto filter on  control itself that allowed the list to shortlist as you typed. This must be a basic function of a combobox especially as my list starts with 90,000 possibles.

I appreciate the tip that the initial list loading is better done in the activate event as the list can refresh each time the combo gets focus.

Roy is right that the simple solution is to use a text box and list box underneath. This was easy and took only 15 minutes to code without flaw.

But the challenge for me was to do this inside a self contained combo box as it is so commonly presented nowadays.  I was srunned that this most basic characteristic is absent by default in excel Vba.

We can load the initial list into the combo in activate but the key problem is as the user types do we use the change event (which is prone to infinite loops}) or perhaps keypress might be best.

Central to creating a workable combobox (tied to a sheet column range}) that autofilers as the user types is that you have to trap 1 item and zero item (no matched entry).  The 1 item upsets the combo list or rowsource. It must be more than one.  So that needs be trapped and manually loaded using additem. The zero match also upsets the list or row source as a feeding range.

When i posted, I really thought that somebody would have a stock coded combobox that links to a sheet range and filters itself as the user types.

I will use the activate and perhaps keypress or keydown events to detect input change. The problem with keydown is that it occurs before the combobox1. Value has changed. The kepress seems better as the input is received by the cotrol. What is the keyup event? I have set test flags but they never seem to fire.
Roy CoxGroup Finance ManagerCommented:
Hi Mike

Unfortunately VBA is no as advanced as VB.  

I did try KeyPress originally if you recall.

I also, thought of positioning the TextBox over the ComboBox and hiding it on exit,
Rory ArchibaldCommented:
If Sheet3.Range("G1").CurrentRegion.Count = 1 Then
ComboBox1.List = Array(Sheet3.Range("G1").Value)

Open in new window

would be the simplest way.
Roy CoxGroup Finance ManagerCommented:
Thanks Rory.

Are you keeping OK?
Rory ArchibaldCommented:
Yes, great thanks, Roy. You?
Is this what you want ???

When you open the workbook it will populate the userform just put data in the combobox and see results in the drop down.

When you press on clear list it actually clears it but automatically after it repopulate it as you were previously advised by calling the enter event.

Martin LissOlder than dirtCommented:
As i recall from my VB6 days there was a property Auto filter on  control itself that allowed the list to shortlist as you typed
That's not correct. You may be thinking of cases where the list would take you to entries that matched (If any) the entered value as you typed it. This was usually referred to as  a typomatic combobox and was achieved via code and the sendmessage API. Note that this can also be achieved in VBA if you want.
mikexmlAuthor Commented:
Thanks for the variety of suggestions.  I will test the ideas and revised code and resort tomorrow .  I am surprised that we werent able to find a link to this having been done successfully before. A combo is such a useful tool and i would have thought most people would start with lists too large to populate the drop down which self filter as they type.

The nice thing about using a separate text box on top of a listbox is that it looks the same but its easy to fire away from both the click and double click events.
Roy CoxGroup Finance ManagerCommented:
The problem is that despite MS increasing Excel's available Rows 90,000entries are a lot for a ComboBox in my opinion.

As I said if you don't want to see two controls simply position the TextBox over the ComboBox.

Option Explicit
Dim Tp As Long, Lt As Long

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'///Keycode for "Enter" is 13
'///hide the Textbox outside the visible part of the UserForm during design
'///make sure both controls are the same size
    If KeyCode = 13 Then
        With Me
            .ComboBox1.Value = .TextBox1.Value
            .TextBox1.Top = Tp
            .TextBox1.Left = Lt
        End With
    End If
End Sub

Private Sub UserForm_Initialize()
    With Me
        Tp = .TextBox1.Top
        Lt = .TextBox1.Left
        .TextBox1.Top = .ComboBox1.Top
        .TextBox1.Left = .ComboBox1.Left
    End With
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.