Do not advance to next cell

[helphv]

I would like to stop at a cell until something is entered into it. I even
want to stop Tab, arrow key also from allowing advancement to the next cell
in my spreadsheet until something is entered into the current cell. Data
Validation still allows Tab and arrow keys to advance, does anyone have any
ideas?

 

[Dave]

Hi,
One way: use a couple of event macros in the sheet window of the VBA editor.
Firstly:

Private Sub Worksheet_Activate()
Range("B2").Select
End Sub

Next:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Range("B2") = "" Then
Range("B2").Select
MsgBox "You must enter something into B2"
End If
Application.EnableEvents = True
End Sub

Regards - Dave.

 

[helphv]

Thanks Dave! This worked great for cell B2, but I really need it to apply to
the entire B column and also for the entire C column in my spreadsheet. Is
this possible?

 

[Dave]

Hi,
Not sure I understand. Does the user have to enter data into every cell in
Columns B & C? That's about 130,000 entries in XL2003, and 2 million in
XL2007!
Dave.

 

[helphv]

Hi Dave,
I would say at least in the first 1000 rows. My spreadsheet contains only 3
fields. We just want the end user to not be able to leave columns B & C
empty in a row once they have entered an ID number in column A. I am using
XL2003. I am also using Data Validation on columns B & C because only a
range of numbers are allowed. I have left Ignor blank unchecked.
Unfortunately, Tab and arrow key allow advancement. Thank you!

 

[Dave]

Hi,
Sorry, this one's a bit beyond me. Try posting again
Dave.

 

[helphv]

Thanks Dave. I appreciate your help.

Hi,
Sorry, this one's a bit beyond me. Try posting again
Dave.