Cell can not be blank

T

Todd

Is there any way to force a user to input data in to a cell and if they move
off when it is blank it will prompt them to input data. I have tried the
validation rules but it does not stop blank cell.
 
C

CLR

Maybe this..........

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Range("a1").Value <> "" Then
MsgBox "cant do that"
Range("a1").Select
Else
End If
End Sub


Vaya con Dios,
Chuck, CABGx3
 
T

Todd

Is there any way to get this to work for 3 other cells. When I put in
another if statement and go to my worksheet when I change to the next cell
where my new if statement goes with, the message box pops up as soon as the
cell is activated. I would like for it to activate when the cell is left if
it is blank. Any ideas. The first code is exatly what I am looking for.
 
C

CLR

Maybe something like this, just for two cells...could be expanded

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Range("a1").Value <> "" Then
MsgBox "cant do that"
Range("a1").Select
ElseIf Not Range("b1").Value <> "" Then
MsgBox "cant do that"
Range("b1").Select
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3
 
T

Todd

That's what I tried and when you tab over to cell b1 it gives you the mesage
box first before leaving the cell. I need it to only come up if you leave
the cell.
 
C

CLR

Sorry Todd, I don't know the answer right off, and I'm about to leave here
and won't be back till Monday. If you want to, you can email me at home and
we can discuss it [email protected] (change the zz to
rr)


Vaya con Dios,
Chuck, CABGx3
 
C

CLR

Hi Todd.....

Well, this one seems to do the job for four cells.........whatcha think?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Range("A1").Value <> "" Then
Range("a1").Select
MsgBox "Cell A1 must contain a value"
Else
If Not Range("b1").Value <> "" Then
Range("b1").Select
MsgBox "Cell B1 must contain a value"
Else
If Not Range("c1").Value <> "" Then
Range("c1").Select
MsgBox "Cell C1 must contain a value"
Else
If Not Range("d1").Value <> "" Then
Range("d1").Select
MsgBox "Cell D1 must contain a value"
End If
End If
End If
End If
End Sub
 
J

John James

Hi Todd,

Just comment out the MsgBox lines and the spreadsheet forces you into a
single cell selection until all forced selections are made - without any
annoying messages. The message seems redundant if there's no other
choice.

Is this what you want?
 
Top