Combobox autocomplete

[tiredofit]

I came across this thread and it seems to work if the list is on the same worksheet as the combobox:

How to use a ComboBox with autocomplete and search as you type.

Hi, I have a ComboBox embedded in a worksheet that references a list of items. Ie: small cat cat big cat small dog dog big dog Right now when I type in 'cat' i only get 'cat' as the suggestion. What I would like is to get all items that contain 'cat'., ie., in this case: small cat cat big...

www.mrexcel.com

Can someone please tell me how to amend it if the list is on a different sheet?

I tried changing this part of the code:

Private Sub ComboBox1_DropButtonClick()
    With Me.ComboBox1
        .List = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)).Value ' CHANGING THIS LINE CAUSES PROGRAM TO CRASH
        .ListRows = Application.WorksheetFunction.Min(6, .ListCount)
        .DropDown
    End With
End Sub

but I get a permission error message.

Thanks

 

[tiredofit]

I came across this thread and it seems to work if the list is on the same worksheet as the combobox:

How to use a ComboBox with autocomplete and search as you type.

Hi, I have a ComboBox embedded in a worksheet that references a list of items. Ie: small cat cat big cat small dog dog big dog Right now when I type in 'cat' i only get 'cat' as the suggestion. What I would like is to get all items that contain 'cat'., ie., in this case: small cat cat big...

www.mrexcel.com

Can someone please tell me how to amend it if the list is on a different sheet?

I tried changing this part of the code:

Private Sub ComboBox1_DropButtonClick()
    With Me.ComboBox1
        .List = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)).Value ' CHANGING THIS LINE CAUSES PROGRAM TO CRASH
        .ListRows = Application.WorksheetFunction.Min(6, .ListCount)
        .DropDown
    End With
End Sub

but I get a permission error message.

Thanks

Not sure why it didn't work before but it does now.

 

[smozgur]

You didn't post an answer that solved the question, so there is no need to mark your last post as the solution. Perhaps someone else who had the same problem night explain the reason.