How to insert an ActiveX Combobox

You will need to enable the Developer tab on your ribbon. Google for help if needed.

On that tab's Controls menu select the Insert toolbox and click on ActiveX controls > ComboBox. Click on the sheet where you want the Cbx and drag the cross hairs to form a rectangle roughly of the intended size.

Observe that the Cbx is framed, meaning selected, Design Mode button in the ribbon is highlighted, meaning enabled, and the Name Bar (top left of your screen, next to the Formula Bar) shows "ComboBox1". You can change the name. I would use something like "CbxInst" [itutions], i.e. a meaningful name but a short one. Later, click the Design Mode button to end design mode and make the Cbx operational. That will also close the Properties window on the left or, rather, end the display of the Cbx's properties. Close the Properties window using the "x" in its top right.

You will be glad to learn that you don't need to set all the available properties but I recommend that you read the list. Height and Width set the size. Top and Left the positon of its top left corner on the sheet. You can change the Name here, too. MatchRequired disallows any entry of the user's fancy if set to True. MatchEntry is important for you because you want the user to enter a letter and let the Cbx jump to the part of the list it indicates. You may want to change the Font - both size and name. Note the that ForeColor is the font's colour, the background is the Backcolor.

Of more immediate use is theListFillRange. Enter "Sheet1!$A$1:$A$87". By entering the sheet's name you can have the data and the Cbx on diferent worksheets. However, I would urge you to use a named range instead of the direct address: Select Sheet1!$A$1:$A$87 and enter "Institutions" in the Name Bar. You can also press Define Name on the ribbon's Formulas tab or use the Name Manager, also found there. Naming your ranges will be helpful in your project. As changes are made to the lists you can manage them all in one place. If you named the range just enter "Institutions" for the ListFillRange property.

Above the ListFilledRange you will see the LinkedCell property. You can enter a sheet address there where you want the selected name to appear. It must be on the same sheet as the Cbx itself. This provides the link - the only link - between the selection in the Cbx and the rest of your workbook. You can use it to retrieve associated data using the VLOOKUP function.

End Design Mode, press the arrow on the Cbx, make a selection from your list and see the result in the LinkedCell. All of this is demonstrated in the attached workbook.

I would urge you not to plan on accessing other workbooks. Instead, consider methods of hiding the sheets with the data. After you close the Cbx Properties you will see the worksheet's properties in that window. The last property is Visible and indicates -1 - xlSheetVisible. You can set this property to xlSheetHidden or xlSheetVeryHidden. A hidden sheet can be made visible by the user from the Format menu. Very hidden sheets aren't listed there. It takes access to the VBA project to unhide them and you can protect that with a password to restrict unauthorised access. Frankly, I don't know if you can apply xlVeryHidden to sheets in a workbook of xlsx format. What I have described here looks like a backdoor to me and might not work. However, I suggest that you consider the implications of enabling VBA for your project and thereby opening the door not only to very hide your data but also eventual automation.

Finally, if the above proves helpful to you, please consider aligning your question to this answer, as a service to the forum's community. Change the title to something like "How to insert an ActiveX Combobox", rephrase your question to match its new title, remove the unrelated attached workbooks and consdier deleting part or all of the discussion. Thank you.