MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How can I force users to enter anything in columnA, if columnB

  Asked By: Ayden    Date: Nov 14    Category: MS Office    Views: 1593

I don't know if this needs to use VBA at all-- if I want to make sure
that once a user enters anything other than blank in a cell in column
B, say B17, then right after he/she presses the ENTER key, I want the
cursor to jump to column A (in this case, that is A17) and wait for
him/her to enter something other than blank. If he/she enters nothing
and tries to leave the cell, an error message dialog box would pop up
and prohibits the user to leave.

Can I do this at all with or without using VBA?



1 Answer Found

Answer #1    Answered By: Noel Peterson     Answered On: Nov 14

Yes it'll need VBA.

You'll need to grab the on-change event for the sheet and check whether it's
column  B cell  that has changed, then grab its row number and look at its A
cell. If that cell is empty, you will need to move the selection to the
empty cell.

Then you'll also need to grab the sheet's selection change event and check
whether it's a column A cell that you're coming from; whether it's still
empty; and whether its column B cell is non-empty. If it is, then you want
to put up an error  message and move the selection back to the cell. (You
will possibly - I'm not sure - also need to return a cancel from the
selection change event to override what the user  was trying to do.)

If I recall correctly, the selection change event will tell you where you're
moving to - rather than where you've come from - so you would also need to
remember which cell you were working on by processing the previous selection

This is fairly advanced VBA programming and might be a bit beyond your
current experience level.