Combobox drop down issue

Question

hello Sir,

I have created a complex excel file where the main function is to select items in column b to create invoice efficiently.

I have done a functionality to make comboboxes popup the suggestion as you type.

I have followed the instructions mentioned at below link to create macro,names,helper columns,combo box properties to make this happen.

https://www.extendoffice.com/documents/excel/2439-excel-drop-down-list-search.html?page\_comment=1

The main problem is, there are 14 comboboxes, and it behaves weirdly every time

1. Sometimes, dropdown appears from a other cell rather than the cell I am editing the value with.
2. Sometimes the selected value in dropdown dont appear in cell.

Please play with it and help me know whats causing the problem.

It will be a good learning for you too to understand this type of behaviours.

The file can be downloaded at https://www.dropbox.com/s/147bcjprvbm6nkb/MASTER%20PROFORMA%20GST%20v1.xlsm?dl=0

Please let me know your inputs

[Moved from:Office/Excel/Windows 10/Office 2016]

Answer 1

I downloaded your file. I am very confused.

1. Why do you have multiple DropDown lists when they are all the same? All of the ComboBoxes should be able to reference one single DropDown list.
2. What is the significance of the 2 columns to the left of the DropDown lists? (One column with all one's and the next with a count of the rows up to the specific row as per the screen shot below with the 2 columns circled in red.)
3. Why use a formula to populate the rows of the DropDownlist? Would it not be better to just list the items. (Perhaps I am missing something here and if so please explain.)
4. If you want the DropDownList assigned to the ComboBox and the ComboBox DropDown to display when the user selects the ComboBox then it needs to be done with the GotFocus event; not the Change event. (The Change event runs every time the user makes a selection in the combo box; not when the User first clicks in it.)

Use the following code in lieu of the Change event code to assign Named range to the ListFillRange and then force the ComboBox DropDown to auto display.

Private Sub ComboBox1_GotFocus()

    ComboBox1.ListFillRange = "DropDownList1"

    Me.ComboBox1.DropDown

End Sub