Trigger Question

C

Curt

column 12 triggers on >10 for one action and on <=0 for another. Is there a
way to have target trigger reduced to 10 when procedure for >10 runs.
Also if column 12 is left blank and row change is done can this be used for
trigger?
following is code I am useing for both triggers
Thanks in advance for any assistance this group is a Blessing to this old dog.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.Column = 12 And Target.Value > 10 And
IsNumeric(Target.Value) Then _
Call CopyDonors(Target)
If Target.Column = (12) And Target.Value <= 0 Then _
Call Copycomp(Target)
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub
Public Sub CopyDonors(ByVal Target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(0, 0)
' recommend disabling events to block extra passes through
' Worksheet_Change caused for changing Donors cells
Application.EnableEvents = False
Set rngPaste = rngPaste.Offset(1, 0)
Range(Target.Offset(0, -7), Target.Offset(0, 0)).Copy _
Destination:=rngPaste
rngPaste.Offset(0, 7) = Target - 10
Application.EnableEvents = True
End Sub
Public Sub Copycomp(ByVal Target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Comp")
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(0, 0)
' recommend disabling events to block extra passes through
' Worksheet_Change caused by changing Comp cells
Application.EnableEvents = False
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste = Range(Target.Offset(0, -7), Target.Offset(0, 0))
Range(Target.Offset(0, -7), Target.Offset(0, 0)).Copy _
Destination:=rngPaste
rngPaste.Offset(0, 7) = Target
Application.EnableEvents = True
End Sub
 
T

Tom Ogilvy

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.Column = 12 And _
Target.Value > 10 And _
IsNumeric(Target.Value) Then
Target.Value = 10
Call CopyDonors(Target)
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub
Also if column 12 is left blank and row change is done can this be used for
trigger?

Have no idea what that means <row change is done >
 
C

Curt

What I am trying to get at is if the person entering data leaves the cell in
column 12 blank with no entry of any kind and goes to next row and continues
new entry. Hope I did a better job of explain
Thanks for this part
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Condition 4
triogger reset 0
workbook event 2
copy certain cells 1
Trigger or code 0
Pass condition formatting across worksheets via vba. 0
Junp start my Select Case Macro 12
offset copy 4

Top