Design Problem : Help users who do not finish editing one cell and try to click combo box in another

[Chrisso]

Hi All

I have a sheet that my users enter data on. I have one cell (say A1)
which is a normal, general cell for a number to be entered. The next
cell (say A2) I use to house a combo box with a pull down list of
options for them to select.

What I have noticed is that some users enter the number into A1 and
then try to select a combo box value for A2 without hitting enter
first in A1. That is, they are still in edit mode in A1, they know in
their head that they are done entering information there so they then
try to use the combo box in A2. Excel still thinks they are editing A1
and does not let the combo box take the click. The users (who are not
Excel savy enoough to know to hit enter and get out of edit mode) are
very confused and think the system is broken.

Has anyone else experienced this problem and designed their way out of
it? Is there some way to handle this with events? Any ideas will be
greatly appreciated.

Thanks in advance,
Chrisso

 

[Tom Ogilvy]

make the combobox hidden until the user enters a value in A1 and hits enter.
Use the change event to capture this action. In that event, make the
combobox visible.

 

[Chrisso]

Thanks Tom - an interesting idea.

Quite by chance I used the list option of cell validation (Data-

Validation) and it does almost the same thing for free!!! It

definitely solves my problem as the selector does not appear unitl the
cell is selected and thus the previous cell is finsihed editing.

The only downside is that the list must exist on the same sheet your
cells are - this is a bummer as I want a master list on a seperate
"Settings" worksheet for the allowable values - but I am sure I can
get around this with a bit of thought.

Thanks again for your ideas Tom and all the posts you have made to
this group over the years. I have certainly used and appreciated a lot
of them.

Chrisso

 

[Doug Glancy]

Chrisso,

You can use a named range from another sheet in your workbook for your data
validation list. For example, name a range on Sheet2 "test". You can then
refer to it from the "List" box in the Data Validation dialog box.

hth,

Doug