Frank was able to help with the selection change event, but I only want to
trigger the event when I've made a change in a row.

I'm looking for code that would create a "Worksheet_Change" event only when
a change of row occurs.


See "Change of Row event" for more details.

Hi

The change event occurs whenever some entrie is changed - no way to bypass
this. You have to check the range the changed cell belongs to at very start
of code, and depending the check result take further actions or exit the
event - thus minimizing the VBA activity.

It's the reason, I myself use Change event very reluctantly. It can
considerably slow down your worksheet sometimes.


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"crazybass2" wrote in message
...
Frank was able to help with the selection change event, but I only want to
trigger the event when I've made a change in a row.

I'm looking for code that would create a "Worksheet_Change" event only
when
a change of row occurs.


See "Change of Row event" for more details.

Hi
also possible but with some more effort. So then should this event
occur now.
- after you have changed something in row x and are now ENTERING the
next change in row y
- after you have changed something in row x and are now SELECTING row y

Maybe you could also explain what you're trying to do with this event
afterwards. There may be a better solution as this gets more
complicated.

--
Regards
Frank Kabel
Frankfurt, Germany

"crazybass2" schrieb im
Newsbeitrag ...
Frank was able to help with the selection change event, but I only
want to
trigger the event when I've made a change in a row.

I'm looking for code that would create a "Worksheet_Change" event
only when
a change of row occurs.


See "Change of Row event" for more details.

I think this may be a fairly complicated deal. Moreso than I thought.

What I have is a 8 column spreadsheet with projects (or tasks). Each task
has a percent complete value, a priority value, a few other descriptors, and
a place for an item number (Column A). I have the sheet broken into two
parts - Incomplete (Range A3:H50), and Complete (Range A55:Hxx) where xx is
the row number of the bottom most cell in the area.

The projects are programmed to sort by percent complete. I want the
worksheet to automatically number new items when a entry is made in a blank
row. However, the item numbers are out of sequence, so my code has a
"worksheet_change" event to find the Max item number and then use the next
number up (Max + 1).

That much of this code works. When I enter my priority number (column B)
the worksheet_change event triggers and inputs the new item number. I'd like
to have it wait until I complete all the data in that row (priority, project,
descritpion, percent complete, etc.)

Here's the code as I have modified it as of yesterday...there may be a few
things that are erroneous because I didn't take out the code you gave
yesterday.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iOldRow As Long
If Target.Cells.Count 1 Then Exit Sub

If iOldRow < 1 Then
iOldRow = Target.Row
ElseIf iOldRow < Target.Row Then
MsgBox "new row selected: " & Target.Row
iOldRow = Target.Row
End If

Dim a, b, c
If Not Intersect(Target, Range("H:H")) Is Nothing Then
b = 55
Do Until c = 0
c = Cells(b, 1)
b = b + 1
Loop
For a = 3 To 50
If Cells(a, 8) = 1 Then
Range(Cells(a, 1), Cells(a, 8)).Cut
Range(Cells(b, 1), Cells(b, 1)).Activate
Paste
End If
Next
Range("A2").Activate
End If
Dim RngAbov As Range
Dim MaxVal As Variant
MaxVal = 0
With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("B:B")) Is Nothing Then Exit Sub
If Intersect(.Cells, Me.Range("B:B")) = "" Then
.Offset(0, -1).Clear
GoTo lastline
End If
Set RngAbov = Me.Range("A:A")

MaxVal = Application.Max(RngAbov)
Application.EnableEvents = False
.Offset(0, -1).Value = MaxVal + 1
.Offset(1, 0).Select
Application.EnableEvents = True

End With
lastline:
Sort
End Sub


Thanks for all the help.

Mike


"Frank Kabel" wrote:

Hi
also possible but with some more effort. So then should this event
occur now.
- after you have changed something in row x and are now ENTERING the
next change in row y
- after you have changed something in row x and are now SELECTING row y

Maybe you could also explain what you're trying to do with this event
afterwards. There may be a better solution as this gets more
complicated.

--
Regards
Frank Kabel
Frankfurt, Germany

"crazybass2" schrieb im
Newsbeitrag ...
Frank was able to help with the selection change event, but I only
want to
trigger the event when I've made a change in a row.

I'm looking for code that would create a "Worksheet_Change" event
only when
a change of row occurs.


See "Change of Row event" for more details.

Hi
then why not check in the worksheet change event if all columns are
filled?

--
Regards
Frank Kabel
Frankfurt, Germany

"crazybass2" schrieb im
Newsbeitrag ...
I think this may be a fairly complicated deal. Moreso than I
thought.

What I have is a 8 column spreadsheet with projects (or tasks). Each
task
has a percent complete value, a priority value, a few other
descriptors, and
a place for an item number (Column A). I have the sheet broken into
two
parts - Incomplete (Range A3:H50), and Complete (Range A55:Hxx) where
xx is
the row number of the bottom most cell in the area.

The projects are programmed to sort by percent complete. I want the
worksheet to automatically number new items when a entry is made in a
blank
row. However, the item numbers are out of sequence, so my code has a
"worksheet_change" event to find the Max item number and then use the
next
number up (Max + 1).

That much of this code works. When I enter my priority number
(column B)
the worksheet_change event triggers and inputs the new item number.
I'd like
to have it wait until I complete all the data in that row (priority,
project,
descritpion, percent complete, etc.)

Here's the code as I have modified it as of yesterday...there may be
a few
things that are erroneous because I didn't take out the code you gave
yesterday.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iOldRow As Long
If Target.Cells.Count 1 Then Exit Sub

If iOldRow < 1 Then
iOldRow = Target.Row
ElseIf iOldRow < Target.Row Then
MsgBox "new row selected: " & Target.Row
iOldRow = Target.Row
End If

Dim a, b, c
If Not Intersect(Target, Range("H:H")) Is Nothing Then
b = 55
Do Until c = 0
c = Cells(b, 1)
b = b + 1
Loop
For a = 3 To 50
If Cells(a, 8) = 1 Then
Range(Cells(a, 1), Cells(a, 8)).Cut
Range(Cells(b, 1), Cells(b, 1)).Activate
Paste
End If
Next
Range("A2").Activate
End If
Dim RngAbov As Range
Dim MaxVal As Variant
MaxVal = 0
With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("B:B")) Is Nothing Then Exit Sub
If Intersect(.Cells, Me.Range("B:B")) = "" Then
.Offset(0, -1).Clear
GoTo lastline
End If
Set RngAbov = Me.Range("A:A")

MaxVal = Application.Max(RngAbov)
Application.EnableEvents = False
.Offset(0, -1).Value = MaxVal + 1
.Offset(1, 0).Select
Application.EnableEvents = True

End With
lastline:
Sort
End Sub


Thanks for all the help.

Mike


"Frank Kabel" wrote:

Hi
also possible but with some more effort. So then should this event
occur now.
- after you have changed something in row x and are now ENTERING
the
next change in row y
- after you have changed something in row x and are now SELECTING
row y

Maybe you could also explain what you're trying to do with this
event
afterwards. There may be a better solution as this gets more
complicated.

--
Regards
Frank Kabel
Frankfurt, Germany

"crazybass2" schrieb im
Newsbeitrag
...
Frank was able to help with the selection change event, but I
only
want to
trigger the event when I've made a change in a row.

I'm looking for code that would create a "Worksheet_Change" event
only when
a change of row occurs.


See "Change of Row event" for more details.