If Target.Row < 2 Then Exit Sub
If Application.CountA(Rows(Target.Row - 1)) And IsEmpty(Target.Value) <
11 Then _
MsgBox "Finish Last Row"
want to check row up to column 11 for empty cells. want this to happen if
user hits enter or down arrow to start a new row. I am off as the msgbox pops
up when entering in row. should not come up till in next roe if blanks left
in tatget row. Have used .CountA(cells (columns to no avail
need help
Thanks

Hi Curt

Try amending to the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim trow As Long
If Target.Row < 2 Then Exit Sub
trow = Application.Max(2, Target.Row - 1)
If Application.CountA(Rows(trow)) < 11 And IsEmpty(Target.Value)
Then
MsgBox "Finish Last Row"
End If
End Sub


--
Regards

Roger Govier


"Curt" wrote in message
...
If Target.Row < 2 Then Exit Sub
If Application.CountA(Rows(Target.Row - 1)) And
IsEmpty(Target.Value) <
11 Then _
MsgBox "Finish Last Row"
want to check row up to column 11 for empty cells. want this to happen
if
user hits enter or down arrow to start a new row. I am off as the
msgbox pops
up when entering in row. should not come up till in next roe if blanks
left
in tatget row. Have used .CountA(cells (columns to no avail
need help
Thanks

Hi Curt -

Copy the first two procedures to the worksheet's module and the third
procedure to a standard module:

Private Sub Worksheet_Activate()
Application.OnKey "{RETURN}", "checkUp"
Application.OnKey "{DOWN}", "checkUp"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{RETURN}"
Application.OnKey "{DOWN}"
End Sub

Sub checkUp()
chkRow = ActiveCell.Row
If chkRow = 1 Then
ActiveCell.Offset(1, 0).Activate
Exit Sub
End If
For Each cel In Range(Cells(chkRow, 1), Cells(chkRow, 11))
If Trim(cel) = "" Then
MsgBox "Finish This Row"
cel.Activate
Exit Sub
End If
Next 'cel
ActiveCell.Offset(1, 0).Activate
End Sub

--
Jay


"Curt" wrote:

If Target.Row < 2 Then Exit Sub
If Application.CountA(Rows(Target.Row - 1)) And IsEmpty(Target.Value) <
11 Then _
MsgBox "Finish Last Row"
want to check row up to column 11 for empty cells. want this to happen if
user hits enter or down arrow to start a new row. I am off as the msgbox pops
up when entering in row. should not come up till in next roe if blanks left
in tatget row. Have used .CountA(cells (columns to no avail
need help
Thanks

Thank You "Jay"
People like you are a great asset to us trying to help others.
thanks Again

"Jay" wrote:

Hi Curt -

Copy the first two procedures to the worksheet's module and the third
procedure to a standard module:

Private Sub Worksheet_Activate()
Application.OnKey "{RETURN}", "checkUp"
Application.OnKey "{DOWN}", "checkUp"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{RETURN}"
Application.OnKey "{DOWN}"
End Sub

Sub checkUp()
chkRow = ActiveCell.Row
If chkRow = 1 Then
ActiveCell.Offset(1, 0).Activate
Exit Sub
End If
For Each cel In Range(Cells(chkRow, 1), Cells(chkRow, 11))
If Trim(cel) = "" Then
MsgBox "Finish This Row"
cel.Activate
Exit Sub
End If
Next 'cel
ActiveCell.Offset(1, 0).Activate
End Sub

--
Jay


"Curt" wrote:

If Target.Row < 2 Then Exit Sub
If Application.CountA(Rows(Target.Row - 1)) And IsEmpty(Target.Value) <
11 Then _
MsgBox "Finish Last Row"
want to check row up to column 11 for empty cells. want this to happen if
user hits enter or down arrow to start a new row. I am off as the msgbox pops
up when entering in row. should not come up till in next roe if blanks left
in tatget row. Have used .CountA(cells (columns to no avail
need help
Thanks

This works beautifully on data worksheet. How do I control it on the other
sheets. The other sheets do not have as many entry cells as data. Know you
can move useing mouse thats ok Could put instructions on other sheets
adviseing this . Would rather control if I can
This is a beautiful piece of code to do the job
Do I need to put it as a procedure in each sheet. I think another option is
to set data sheet up different from all others as others are same size.
Thanks Much

"Jay" wrote:

Hi Curt -

Copy the first two procedures to the worksheet's module and the third
procedure to a standard module:

Private Sub Worksheet_Activate()
Application.OnKey "{RETURN}", "checkUp"
Application.OnKey "{DOWN}", "checkUp"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{RETURN}"
Application.OnKey "{DOWN}"
End Sub

Sub checkUp()
chkRow = ActiveCell.Row
If chkRow = 1 Then
ActiveCell.Offset(1, 0).Activate
Exit Sub
End If
For Each cel In Range(Cells(chkRow, 1), Cells(chkRow, 11))
If Trim(cel) = "" Then
MsgBox "Finish This Row"
cel.Activate
Exit Sub
End If
Next 'cel
ActiveCell.Offset(1, 0).Activate
End Sub

--
Jay


"Curt" wrote:

If Target.Row < 2 Then Exit Sub
If Application.CountA(Rows(Target.Row - 1)) And IsEmpty(Target.Value) <
11 Then _
MsgBox "Finish Last Row"
want to check row up to column 11 for empty cells. want this to happen if
user hits enter or down arrow to start a new row. I am off as the msgbox pops
up when entering in row. should not come up till in next roe if blanks left
in tatget row. Have used .CountA(cells (columns to no avail
need help
Thanks