Many of the solutions that I build involve populating a drop-down list on a custom form. This is achieved using a few lines of VBA code.
In most cases, the end-user wants to be able to update the items on the drop-down list without coming back to me. The easiest way to do this is to base the drop-down list on a range of cells.
The code, which runs when the form is loaded onto screen, selects B1 and loops through the cells in column B, until it finds a blank cell. The value in each cell becomes an item in the drop-down list.
However, if someone moves the data to another part of the worksheet or inserts rows or columns, this will stop the macro running.
A better solution is to assign a name to B1. In this example, I’ve named B1 “CountryStartList”. This way, if the list of countries is moved, the name moves with it. The macro will refer to CountryStartList instead of B1
