Controlling Active X Combo Box using VBA

[MixedUpExcel]

Hi,

I've not found what I am looking for, so if someone can point me in the right direction if this has been done before please?

I have a Combo Box on Sheet1

I have a list of items (approx 200) on Sheet2 in Column A (which will be my range and is set as a dynamic named range) - also, it doesn't start in A1 (data above the range which I don't want to use and also data below the range that I don't want to use)

As a working Combo Box list - the above works fine, however, I've been asked to make it simpler to find products in the drop down list.

I would like the user to be able to type into the Combo Box field and it will show any results which contain what they are searching (regardless of whether they put the first letter, middle letters or end letters into the search field in the Combo Box).

I would also like the Combo box to start with 10 rows but if the user, say, gets down to 3 options, it only shows 3 rows (not too important if it can't be done)

Can this be done with VBA and where should I look to find out how?

Thanks in advance.

Simon

 

[Worf]

Hi Simon

I'm going offline now but will work on this tomorrow...

 

[AlphaFrog]

Try this.

In the code, change the name of ComboBox1 and the Named Range "MyList" to suit.

To install the code:

• Right-click on the Sheet1 tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=#ff0000]ComboBox1[/COLOR]_Change()
    [COLOR=darkblue]Dim[/COLOR] v [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], j [COLOR=darkblue]As[/COLOR] Long
    [COLOR=darkblue]With[/COLOR] [COLOR=#ff0000]ComboBox1[/COLOR]
        .MatchEntry = fmMatchEntryNone
        .ListRows = 10
        [COLOR=darkblue]If[/COLOR] .Text = "" [COLOR=darkblue]Or[/COLOR] .ListIndex > -1 [COLOR=darkblue]Then[/COLOR]
            .ListFillRange = "[COLOR=#ff0000]MyList[/COLOR]"
        [COLOR=darkblue]Else[/COLOR]
            v = Application.Transpose(Range("[COLOR=#ff0000]MyList[/COLOR]"))
            .ListFillRange = ""
            [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](v)
                [COLOR=darkblue]If[/COLOR] LCase(v(i)) [COLOR=darkblue]Like[/COLOR] "*" & LCase(.Text) & "*" [COLOR=darkblue]Then[/COLOR]
                    j = j + 1
                    v(j) = v(i)
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] i
            [COLOR=darkblue]If[/COLOR] j = 0 [COLOR=darkblue]Then[/COLOR]
                .List = Array("No Match")
            [COLOR=darkblue]Else[/COLOR]
                [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] v(1 [COLOR=darkblue]To[/COLOR] j)
                .List = v                
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If
[/COLOR]            .DropDown
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[MixedUpExcel]

Hi AlphaFrog,

Thanks for the quick reply.

I've created a test workbook to try it out on first before I place it onto my main workbook.

Simply:
New Workbook
Sheet2 - added a list of 20 names with various numbers eg. Simon5, Simon6 etc in cells A11 to A210 and named that range as MyList
Sheet1 - inserted new Active X Combo Box and linked it to range 'MyList'

Checked it and the drop down list was working fine

Followed your steps above and added the code into the Sheet1 Module

Inserted the letter S into the Combo Box and got the following error message:

Run-time error '1004':
Method 'Range' of object'_Worksheet' failed

Clicked on Debug and the following line was highlighted in yellow on Line12:

v = Application.Transpose(Range("MyList"))

Do I need to set the Range somewhere else or is it something completely different?

Thanks again.

Simon

 

[AlphaFrog]

I should have qualified the sheet where the list is located. Add this in red...

v = Application.Transpose(Sheets("Sheet2").Range("MyList"))

Also, change the MatchEntry property in your combobox's properties window to fmMatchEntryNone

 

[MixedUpExcel]

Hi AlphaFrog,

Perfect.

That is just what I wanted.

Thank you so much for helping me out with that

Simon

 

[AlphaFrog]

I take it the suggestion to qualify the sheet fixed the error?

I have an older version of Excel. Save your file as .xls file format. You can upload your file to a file share site and post the link here.

 

[MixedUpExcel]

Yes. That sorted it.

My problem (why I edited my last post) was I'd recreated the test sheet when you responded (as I'd mistakenly closed and not saved my test book earlier) so when I came to paste your code in - I'd put it in sheet2 and not sheet1.

As soon as I corrected that... worked just how I'd hoped.

Thanks.

Simon

 

[AlphaFrog]

You're welcome. Thanks for the feedback.

 

[MixedUpExcel]

Hi AlphaFrog,

If you see this, I have created a new thread with a different query but have encountered a problem where it links to the above code (and I'm not sure why it even tries to run the above macro) and creates a loop that I have to CTRL + BREAK to get out of.

Wondering if you had any ideas?

http://www.mrexcel.com/forum/excel-...down-box-link-cell-only-show-option-list.html

Thanks in advance.

Simon