Build a Smart Excel Combobox

Build a Smart Combo Box

Like most things in life, Excel VBA users mature as they write code overtime. Every time we search the internet for a vexing VBA problem we learn something. Of course, I hope you take the time to look at more than one solution because there may others that are better or more appropriate.  In this post, I walk you through how to Build a Smart Excel Combobox.  It will take your coding to the next level.

Roughly defined the excel users fall into these three categories and I included a few attributes for each Type

Newbie



Journeyman



Expert/Craftsman

Will travel to every cell, page, worksheet they are using on
Probably records much of the macros he creates
Tends to use the activecell object often

Address cell, page, worksheet not by travel but by location 
Occasionally records macros to grab snippets from them tends to use the Cells object often

All the Journeyman does and
Creates dynamic smart forms/controls
Endeavors to build bulletproof spreadsheets

In this post, we tackle: “Creates dynamic smart forms/controls” but, before I do that let’s define the term machine learning.  Yes, it is a popular term that evokes images of the terminator for some.  But forget that for now and let’s jump back to reality, Wikipedia says:

Machine learning (ML) is the study of computer algorithms that improve automatically through experience.

Sounds a bit complicated at first right, but in reality, you should be using machine learning to improve your code.  Allow me to re-introduce you to the humble Excel combobox.
Build a Smart Combo Box
      Build a Smart Excel Combobox
In this example, Our fictitious business uses 50 different warehouses throughout the country to ship goods from. They employ 5 dispatchers to enter the system to create shipping orders. Each of these dispatchers specializes in where they ship from. They also are cover for each other’s vacations. So each Dispatcher needs access to all 50 locations.
Anyone who has had to use a dropdown with lists of 50 on it knows it can be both frustrating, time-consuming, and feel a bit clunky. Your thoughts should run to How might I improve it? The answer is machine learning.
Watch my video below and learn how to Build a Smart Excel Combobox.

Smart Combobox




<- Download the code to Build a Smart Combobox
Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me
Tagged , , , , , . Bookmark the permalink.

Comments are closed.