autocomplete drop down list

[Bedford]

I have several sheets in a workbook. A list of product codes, 76 rows and growing in one sheet. Another sheet contains an invoice template I created. In the invoice sheet, left column is where I'm hoping to place a drop down list, that when I start typing it will automatically begin filling in the matching product code from the corresponding list in the other sheet.

Much appreciate any help, I must tell, I'm working on a mac, and have both Office 2011 & 2016 installed, likely returning the later for refund as it's lighter in functionality to the 2011 version. I'm now finding out Excel for mac does not hold a candle to the functionally and features of the Windows version.

Thank you,
Doug.

 

[Peter_SSs]

I don't know about Macs, but here is some info about setting up AutoComplete with drop-down lists.

 

[Bedford]

Peter, thank you for responding to my situation. It seems Excel for Mac is considerably different than that of the Windows version. The link you provided above, it reads; "You can use Data Validation to create a dropdown list of options in a cell. However, the list font can't be changed, nor can the number of visible rows, which has a maximum of eight. Also, Data Validation doesn't have an AutoComplete feature, which finds matching items in the list as you start to type.
Double-click on a cell that contains a data validation list, and the combo box appears. The combo box's font size can be set, more than 8 rows can be displayed, and autocomplete can be enabled."
My plight with MacExcel, double clicking as pointed out here doesn't work, I can with much effort get a combo box into the sheet, but there are no properties to enable any editing or customization. I've read other blogs, and even some support pages on the MS support pages whereby there are many users in the same position, unable to use VB particularly in MacExcel 2016.
My search continues.
Thanks again,
Doug.

 

[Jenvg71]

Peter, I have spent the last couple of hours trying to figure out how to create a list that when you start typing the name from the list (has over 7000 names) it will pop up and there is no need to search the whole list. I am so confused with all the formulas.. please tell me there is an easier way.... I am grateful for any help.

 

[Peter_SSs]

Peter, I have spent the last couple of hours trying to figure out how to create a list that when you start typing the name from the list (has over 7000 names) it will pop up and there is no need to search the whole list. I am so confused with all the formulas.. please tell me there is an easier way.... I am grateful for any help.

Welcome to the MrExcel board!

Have you been to the link I provided in post #2? If you read & follow all that information carefully, and watch the video provided there, that is the easiest way I know of to reduce the list &/or auto-complete as you type to choose from a given list.

 

[Jenvg71]

Thank you for your reply. I found that video earlier today and tried doing the steps and it did not work for me. That is what brought me to this blog to see if i could find an answer. Thank you anyway.

 

[Akuini]

Thank you for your reply. I found that video earlier today and tried doing the steps and it did not work for me. That is what brought me to this blog to see if i could find an answer. Thank you anyway.

Are you talking about activex combobox?
I created an example showing a combobox that search the list as you type.
(but AFAIK activex control won't work in Mac)
This is the code:

Private Sub ComboBox1_Change()

Dim d As Object, vList1, i As Long
vList1 = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value
   
With ComboBox1
If .Value <> "" And IsError(Application.Match(.Value, vList1, 0)) Then
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vList1) To UBound(vList1)
        If LCase(vList1(i, 1)) Like Replace(LCase(.Value), " ", "*") & "*" Then
          d(vList1(i, 1)) = 1
        End If
    Next
       .List = d.keys
       .DropDown
End If
End With
End Sub


Private Sub ComboBox1_GotFocus()
ComboBox1.MatchEntry = fmMatchEntryNone
ComboBox1.Value = ""
End Sub

Private Sub ComboBox1_DropButt*******()
Dim vList, d As Object, i As Long
If ComboBox1.Value = vbNullString Then
vList = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = 1
    Next
       ComboBox1.List = d.keys
End If
End Sub

This is the workbook:
https://www.dropbox.com/s/jnro36pznhq71c2/deCombobox - search as you type 4.xlsm?dl=0

 

[Bedford]

Hi Akuini, I'm not certain if Jenvg71 and I are seeking the same outcome, however, I wasn't looking for a combobox, either way it seems Excel for the Mac doesn't offer the full customization of comboboxes that it affords the Windows version. So what I have is a range of cells in a column that have data validation linked to a column in another sheet, if that's the correct terminology. What I was hoping for, was that when I begin typing it would begin to offer a selection of matching data from the list in the other sheet so that I wouldn't have to select from the hundreds of options in the drop down menu in that cell. It will however offer matching selections from cells above that have been previously entered, but only those that have been previously entered as opposed to searching and offering from the complete list that is linked through the data validation.

I hope I'm not being too confusing in my explanation.
Thanks for your offering below...
Doug.

Are you talking about activex combobox?
I created an example showing a combobox that search the list as you type.
(but AFAIK activex control won't work in Mac)
This is the code:

Private Sub ComboBox1_Change()

Dim d As Object, vList1, i As Long
vList1 = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value
   
With ComboBox1
If .Value <> "" And IsError(Application.Match(.Value, vList1, 0)) Then
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vList1) To UBound(vList1)
        If LCase(vList1(i, 1)) Like Replace(LCase(.Value), " ", "*") & "*" Then
          d(vList1(i, 1)) = 1
        End If
    Next
       .List = d.keys
       .DropDown
End If
End With
End Sub


Private Sub ComboBox1_GotFocus()
ComboBox1.MatchEntry = fmMatchEntryNone
ComboBox1.Value = ""
End Sub

Private Sub ComboBox1_DropButt*******()
Dim vList, d As Object, i As Long
If ComboBox1.Value = vbNullString Then
vList = Sheets("deList").Range("A2", Sheets("deList").Cells(Rows.Count, "A").End(xlUp)).Value
    Set d = CreateObject("scripting.dictionary")
    For i = LBound(vList) To UBound(vList)
          d(vList(i, 1)) = 1
    Next
       ComboBox1.List = d.keys
End If
End Sub

This is the workbook:
https://www.dropbox.com/s/jnro36pznhq71c2/deCombobox - search as you type 4.xlsm?dl=0

 

[Akuini]

Hi Akuini, I'm not certain if Jenvg71 and I are seeking the same outcome, however, I wasn't looking for a combobox, either way it seems Excel for the Mac doesn't offer the full customization of comboboxes that it affords the Windows version. So what I have is a range of cells in a column that have data validation linked to a column in another sheet, if that's the correct terminology.
Doug.

Sorry for the confusion, I was responding to Jenvg71. I think Jenvg71 should start a new thread because the issue is different from this thread issue.
And I'm not familiar with Excel for Mac, so I can't help you with that.
Again, sorry for the confusion.