Trigger Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?
Click to expand...

Have no idea what that means <row change is done >
 
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
 
Back
Top