How to do the following by pressing the down arrow or up arrow key?

[cyberdude]

Hi,


I wrote a macro to do the following thing:


I move from B1 to B100 and for each stop over a B cell, an input box
prompts me to key in a number to be the value in the current B cell. I

press enter to move to the next cell. Then, an input box popps up
asking me for a number again. The process goes on until B100.


Now, I want to do a slightly different thing: I want to move up and
down the B cells casually and easily by pressing the down arrow or up
arrow key without pressing the enter key at all. Is it possible to do
that? Let me give you an example. Suppose, I am on B3. An input box
shows up and asks me for a number. I enter 3. I want 3 to be B3's
value, and I hope to move up one cell by pressing the up arrow key or
down one cell by pressing the down arrow key without using the enter
key at all. Is doing that possible? Thanks.


David

 

[Bernie Deitrick]

David,

To use the arrow keys, you can't be within a macro. To acheive that, use Data Validation on each
cell, customizing the input message to be an appropriate prompt for each cell. Then you can use the
arrow keys, and get prompted.

HTH,
Bernie
MS Excel MVP

 

[cyberdude]

Hi,

To use the arrow keys, you can't be within a macro. To acheive that, use Data Validation on each
cell, customizing the input message to be an appropriate prompt for each cell. Then you can use the
arrow keys, and get prompted.

Could you tell me more about Data Validation and the other steps in
your mind that may help me do the job? Thank you in advance.

David

 

[Bernie Deitrick]

David,

Select a cell where, let's say, you want someone to enter their name. Choose Data / Validation...
then on the Settings tab, choose Text Length under Allow, and set it to a minimum of 1 using the
other boxes. Choose the "input message" tab, and type "Enter your name" in the message box, and
make sure that you check "Show input message when cell is selected"

Then in the cell below, do the same, except choose, let's say, Allow: "Whole Number" "Between"
Min: "1" and Max "10" (use the four boxes). Then on the input message tab, set the message to be
"Choose a number between one and ten"

Then when you arrow down between those cells, you will see the two input messages, and still be able
to arrow around....

HTH,
Bernie
MS Excel MVP

 

[cyberdude]

Thank you so much!

David

Bernie Deitrick 寫é“：