I need to distribute a workbook as a data entry device that applies a
few constriants. In column A I have a list of Names. In Column B a
City needs to be selected from a limited list for each Name. The text
below describes "nearly there" options I've explored; but I'd like
opinion from experience. The objective is to be easy to for the user
to work around, reliable for its purpose of constraining values, and
ease of build and maintenance.

1. Is there a good way to replicate a Forms control (e.g., the
listbox) down an Excel column, so that it both addresses the cell
beneath and visually aligns with it? The Forms controls don't seem to
be set up for programmed assignment the way the Control Toolbox ones
are (macro recorder code isn't easily adapted).

2. I have used Selection_Change event to actually move a *single*
control from the (ActiveX) Control Toolbox whenever a user enters a
cell within the column. However, it is uncomfortable for data entry
as one goes down the column: if the control is a Listbox with generous
row display, the control covers cells below and it's hard to commit a
value and move down a cell. If it's a ListBox with a one-cell row
display, selection of an item is difficult. If the control is a
Combobox, it appears to permit values not in the list, irrespective of
its MatchRequired property. (The Help does warn that some containers
don't enforce this property).

3. Without the use of code, both sets of tools seems to allow blind
data entry into the cell behind. What do designers do? Hide the
column of references &/or lock/unlock the code?

4. I'm willing to use a form if that's the best way forward. However,
my aim is to allow flexible navigation whilst controlling data values.
Basically, so that the people "feel" they are using a spreadsheet,
but with the selection objects collecting data naturally as they are
encountered.

Tks