MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

input at active cell like inputbox

  Asked By: Mada    Date: Aug 28    Category: MS Office    Views: 2342

is there the statement to entry data like inputbox but without box area?
cursor at the active cell.

for example :
macro is running.
cursor at A1 i write 5 then press enter, cursor automaticaly move to B1 and
i write 7 then press enter, cursor automaticaly move to C1 and i write
"=A1*B1" then press enter.
now cursor will be moved to A2, B2, C2 and so on.



2 Answers Found

Answer #1    Answered By: Audris Schmidt     Answered On: Aug 28

Excel offers some of that directly. Just configure the "Move selection after
enter" option (in the Edit tab of Options) to do the move  to the right.

Or, you can trap the worksheet change event that will occur when you type in the
cell and press  enter or otherwise leave the cell. You'll need to determine
where the cell  is (Row and Column properties) and move the focus as appropriate.

Unfortunately, the change event will fire regardless of how you committed the
change (e.g. it also fires if you use an arrow key or if you click the mouse on
a different cell), so it is of dubious utility for this activity. Also, the
event will not fire if you don't actually change the cell's contents, but just
hit enter.

You can associate input  boxes with cells instead of forms, if you like, but you
might as well just create a data-entry form if you want this level of assistance
from Excel.

Another option is to use Excel's "Move selection" with a bit of an assist from
VBA. If you set the option to do a move right, you will get an Exit event from
the cell when you hit enter. You can ignore most of these, but look for an exit
from a Column C cell and zip the focus down and left. Alternatively, you could
use the SelectionChange event that will occur when the focus moves to a new
cell. If the selection has gone to a cell in Column D, you want to drop down
one and move to Column A. Again, these capabilities are not mouse or arrow-key
tolerant, and may be a bit of a nuisance. At least the "selection change" one
allows you to back up to previous cells and make corrections.

Have a play and see what works for you. Events are easy, and fun to do.

Answer #2    Answered By: Rory Anderson     Answered On: Aug 28

Try this..


Didn't find what you were looking for? Find more on input at active cell like inputbox Or get search suggestion and latest updates.