software

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(quot;a1quot;).Value lt;gt; quot;quot; Then
MsgBox quot;cant do thatquot;
Range(quot;a1quot;).Select
Else
End If
End SubVaya con Dios,
Chuck, CABGx3
quot;Toddquot; wrote:

gt; Is there any way to force a user to input data in to a cell and if they move
gt; off when it is blank it will prompt them to input data. I have tried the
gt; 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.

quot;CLRquot; wrote:

gt; Maybe this..........
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; MsgBox quot;cant do thatquot;
gt; Range(quot;a1quot;).Select
gt; Else
gt; End If
gt; End Sub
gt;
gt;
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;Toddquot; wrote:
gt;
gt; gt; Is there any way to force a user to input data in to a cell and if they move
gt; gt; off when it is blank it will prompt them to input data. I have tried the
gt; gt; 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(quot;a1quot;).Value lt;gt; quot;quot; Then
MsgBox quot;cant do thatquot;
Range(quot;a1quot;).Select
ElseIf Not Range(quot;b1quot;).Value lt;gt; quot;quot; Then
MsgBox quot;cant do thatquot;
Range(quot;b1quot;).Select
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3quot;Toddquot; wrote:

gt; Is there any way to get this to work for 3 other cells. When I put in
gt; another if statement and go to my worksheet when I change to the next cell
gt; where my new if statement goes with, the message box pops up as soon as the
gt; cell is activated. I would like for it to activate when the cell is left if
gt; it is blank. Any ideas. The first code is exatly what I am looking for.
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Maybe this..........
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;a1quot;).Select
gt; gt; Else
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Toddquot; wrote:
gt; gt;
gt; gt; gt; Is there any way to force a user to input data in to a cell and if they move
gt; gt; gt; off when it is blank it will prompt them to input data. I have tried the
gt; gt; gt; 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.

quot;CLRquot; wrote:

gt; Maybe something like this, just for two cells...could be expanded
gt;
gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; MsgBox quot;cant do thatquot;
gt; Range(quot;a1quot;).Select
gt; ElseIf Not Range(quot;b1quot;).Value lt;gt; quot;quot; Then
gt; MsgBox quot;cant do thatquot;
gt; Range(quot;b1quot;).Select
gt; End If
gt; End Sub
gt;
gt; hth
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt; quot;Toddquot; wrote:
gt;
gt; gt; Is there any way to get this to work for 3 other cells. When I put in
gt; gt; another if statement and go to my worksheet when I change to the next cell
gt; gt; where my new if statement goes with, the message box pops up as soon as the
gt; gt; cell is activated. I would like for it to activate when the cell is left if
gt; gt; it is blank. Any ideas. The first code is exatly what I am looking for.
gt; gt;
gt; gt; quot;CLRquot; wrote:
gt; gt;
gt; gt; gt; Maybe this..........
gt; gt; gt;
gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; gt; MsgBox quot;cant do thatquot;
gt; gt; gt; Range(quot;a1quot;).Select
gt; gt; gt; Else
gt; gt; gt; End If
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Vaya con Dios,
gt; gt; gt; Chuck, CABGx3
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Toddquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Is there any way to force a user to input data in to a cell and if they move
gt; gt; gt; gt; off when it is blank it will prompt them to input data. I have tried the
gt; gt; gt; gt; 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
quot;Toddquot; wrote:

gt; That's what I tried and when you tab over to cell b1 it gives you the mesage
gt; box first before leaving the cell. I need it to only come up if you leave
gt; the cell.
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Maybe something like this, just for two cells...could be expanded
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;a1quot;).Select
gt; gt; ElseIf Not Range(quot;b1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;b1quot;).Select
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt; quot;Toddquot; wrote:
gt; gt;
gt; gt; gt; Is there any way to get this to work for 3 other cells. When I put in
gt; gt; gt; another if statement and go to my worksheet when I change to the next cell
gt; gt; gt; where my new if statement goes with, the message box pops up as soon as the
gt; gt; gt; cell is activated. I would like for it to activate when the cell is left if
gt; gt; gt; it is blank. Any ideas. The first code is exatly what I am looking for.
gt; gt; gt;
gt; gt; gt; quot;CLRquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Maybe this..........
gt; gt; gt; gt;
gt; gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; MsgBox quot;cant do thatquot;
gt; gt; gt; gt; Range(quot;a1quot;).Select
gt; gt; gt; gt; Else
gt; gt; gt; gt; End If
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Vaya con Dios,
gt; gt; gt; gt; Chuck, CABGx3
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Toddquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there any way to force a user to input data in to a cell and if they move
gt; gt; gt; gt; gt; off when it is blank it will prompt them to input data. I have tried the
gt; gt; gt; gt; gt; 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(quot;A1quot;).Value lt;gt; quot;quot; Then
Range(quot;a1quot;).Select
MsgBox quot;Cell A1 must contain a valuequot;
Else
If Not Range(quot;b1quot;).Value lt;gt; quot;quot; Then
Range(quot;b1quot;).Select
MsgBox quot;Cell B1 must contain a valuequot;
Else
If Not Range(quot;c1quot;).Value lt;gt; quot;quot; Then
Range(quot;c1quot;).Select
MsgBox quot;Cell C1 must contain a valuequot;
Else
If Not Range(quot;d1quot;).Value lt;gt; quot;quot; Then
Range(quot;d1quot;).Select
MsgBox quot;Cell D1 must contain a valuequot;
End If
End If
End If
End If
End Sub
quot;Toddquot; gt; wrote in message
...
gt; That's what I tried and when you tab over to cell b1 it gives you the
mesage
gt; box first before leaving the cell. I need it to only come up if you leave
gt; the cell.
gt;
gt; quot;CLRquot; wrote:
gt;
gt; gt; Maybe something like this, just for two cells...could be expanded
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;a1quot;).Select
gt; gt; ElseIf Not Range(quot;b1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;b1quot;).Select
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt; quot;Toddquot; wrote:
gt; gt;
gt; gt; gt; Is there any way to get this to work for 3 other cells. When I put in
gt; gt; gt; another if statement and go to my worksheet when I change to the next
cell
gt; gt; gt; where my new if statement goes with, the message box pops up as soon
as the
gt; gt; gt; cell is activated. I would like for it to activate when the cell is
left if
gt; gt; gt; it is blank. Any ideas. The first code is exatly what I am looking
for.
gt; gt; gt;
gt; gt; gt; quot;CLRquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Maybe this..........
gt; gt; gt; gt;
gt; gt; gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; gt; gt; MsgBox quot;cant do thatquot;
gt; gt; gt; gt; Range(quot;a1quot;).Select
gt; gt; gt; gt; Else
gt; gt; gt; gt; End If
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Vaya con Dios,
gt; gt; gt; gt; Chuck, CABGx3
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Toddquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there any way to force a user to input data in to a cell and if
they move
gt; gt; gt; gt; gt; off when it is blank it will prompt them to input data. I have
tried the
gt; gt; gt; gt; gt; 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:
gt; That's what I tried and when you tab over to cell b1 it gives you the
gt; mesage
gt; box first before leaving the cell. I need it to only come up if you
gt; leave
gt; the cell.
gt;
gt; quot;CLRquot; wrote:
gt; gt; Maybe something like this, just for two cells...could be expanded
gt; gt;
gt; gt; Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
gt; gt; If Not Range(quot;a1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;a1quot;).Select
gt; gt; ElseIf Not Range(quot;b1quot;).Value lt;gt; quot;quot; Then
gt; gt; MsgBox quot;cant do thatquot;
gt; gt; Range(quot;b1quot;).Select
gt; gt; End If
gt; gt; End Sub
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt;--
John James
------------------------------------------------------------------------
John James's Profile: www.excelforum.com/member.php...oamp;userid=32690
View this thread: www.excelforum.com/showthread...hreadid=532705