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

it is possible through Datavalidation
it is available at Data/ Data Validation Menu
Selct the column you want to accept data for Say City
Selct Data/Validation Menu
In the dialogue box Selct List in allow combo
in Source Select the range in single Colomn where City data is residing

it is possible only when your master data (cities) is resides on the same
sheet where you are validating

It simpler than going for VBA

Rajan



David Powell wrote in message
om...
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

Your statement: "..it is possible only when your master data (cities) is
resides on the same
sheet where you are validating." is not true, Rajan.

You may create a named range on any sheet within the workbook and use it to
add Data Validation.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
"Rajan" wrote in message
...
it is possible through Datavalidation
it is available at Data/ Data Validation Menu
Selct the column you want to accept data for Say City
Selct Data/Validation Menu
In the dialogue box Selct List in allow combo
in Source Select the range in single Colomn where City data is residing

it is possible only when your master data (cities) is resides on the same
sheet where you are validating

It simpler than going for VBA

Rajan



David Powell wrote in message
om...
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

This might be a good place to start:
http://www.contextures.com/xlDataVal01.html

Ed


" EZ Money" wrote in message
...
Your statement: "..it is possible only when your master data (cities) is
resides on the same
sheet where you are validating." is not true, Rajan.

You may create a named range on any sheet within the workbook and use it
to
add Data Validation.

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
"Rajan" wrote in message
...
it is possible through Datavalidation
it is available at Data/ Data Validation Menu
Selct the column you want to accept data for Say City
Selct Data/Validation Menu
In the dialogue box Selct List in allow combo
in Source Select the range in single Colomn where City data is residing

it is possible only when your master data (cities) is resides on the
same
sheet where you are validating

It simpler than going for VBA

Rajan



David Powell wrote in message
om...
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