Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
crazybass2
 
Posts: n/a
Default Worksheet Row Change event

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.


  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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.




  #3   Report Post  
Frank Kabel
 
Posts: n/a
Default

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.



  #4   Report Post  
crazybass2
 
Posts: n/a
Default

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.




  #5   Report Post  
Frank Kabel
 
Posts: n/a
Default

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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change Axes Scale Dynamically Fysh Charts and Charting in Excel 3 December 16th 04 03:57 PM
How do link to a remote worksheet using the path value in a field? Michael T. Links and Linking in Excel 3 December 11th 04 09:45 AM
Change of Row event crazybass2 Excel Discussion (Misc queries) 7 December 7th 04 07:21 PM
Linking items GREATER THAN O on another worksheet in the same Work Eddie Shapiro Excel Discussion (Misc queries) 4 December 1st 04 03:55 PM
Worksheet name and Backward compatibility Rich Excel Discussion (Misc queries) 3 November 30th 04 07:10 PM


All times are GMT +1. The time now is 07:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"