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.

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



"Todd" wrote:

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.

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.

"CLR" wrote:

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



"Todd" wrote:

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.

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


"Todd" wrote:

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.

"CLR" wrote:

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



"Todd" wrote:

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.

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.

"CLR" wrote:

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


"Todd" wrote:

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.

"CLR" wrote:

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



"Todd" wrote:

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.

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 (change the zz to
rr)


Vaya con Dios,
Chuck, CABGx3



"Todd" wrote:

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.

"CLR" wrote:

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


"Todd" wrote:

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.

"CLR" wrote:

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



"Todd" wrote:

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.

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



"Todd" wrote in message
...
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.

"CLR" wrote:

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


"Todd" wrote:

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.

"CLR" wrote:

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



"Todd" wrote:

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.

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?




Todd Wrote:
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.

"CLR" wrote:
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



--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532705