worksheet
 

is there a way to have data that is copied from another worksheet by a
procedure trigger a procedure on the worksheet that receives the data. This
would be app to each row as data is entered in parent worksheet.
This community sure is a great bunch.
Thanks

worksheet
 

Yes, use some code in the receiving worksheet's _Change() event handler to
deal with it. The Change event is triggered when a value in a cell on the
sheet changes either by user input or as the result of VB code altering a
cell's contents. I am interpreting your term "procedure" to mean the result
of some VB code action.

It does NOT fire if the change results because of a formula
calculation/recalculation in a cell.

You'd probably want to include a statement at the start of your event
handler as
Application.EnableEvents = False
and then do your processing dealing with the change(s)
and then just before exiting the event handler:
Application.EnableEvents = True

The reason for this is that each change in each cell can trigger the Change
event, and since I imagine you may be working with more than one cell at a
time, you don't want to call your event handler 50 times for a 50 cell change.

"Curt" wrote:

is there a way to have data that is copied from another worksheet by a
procedure trigger a procedure on the worksheet that receives the data. This
would be app to each row as data is entered in parent worksheet.
This community sure is a great bunch.
Thanks

worksheet
 

The caluclation is on the sheet that posts to the one I want to trigger. Do I
understand you correctly that the parent sheet calucation stops the copy from
triggering. Following is the code I have it works on data entry on worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.Column = 8 And Target.Value And IsNumeric(Target.Value) Then _
Call CopyMailD(Target)
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub
Thanks

"JLatham" wrote:

Yes, use some code in the receiving worksheet's _Change() event handler to
deal with it. The Change event is triggered when a value in a cell on the
sheet changes either by user input or as the result of VB code altering a
cell's contents. I am interpreting your term "procedure" to mean the result
of some VB code action.

It does NOT fire if the change results because of a formula
calculation/recalculation in a cell.

You'd probably want to include a statement at the start of your event
handler as
Application.EnableEvents = False
and then do your processing dealing with the change(s)
and then just before exiting the event handler:
Application.EnableEvents = True

The reason for this is that each change in each cell can trigger the Change
event, and since I imagine you may be working with more than one cell at a
time, you don't want to call your event handler 50 times for a 50 cell change.

"Curt" wrote:

is there a way to have data that is copied from another worksheet by a
procedure trigger a procedure on the worksheet that receives the data. This
would be app to each row as data is entered in parent worksheet.
This community sure is a great bunch.
Thanks

worksheet
 

I believe you're good to go with that. The Application.EnableEvents = False
statement will keep the change event from triggering when you do whatever is
being done in CopyMailD(Target) gets executed.

The If statement you have will deal with the values entered into column H
and ignore changes in other cells on the sheet, ignoring text or emptied
cells (as with [Del]) that trigger the event.

"Curt" wrote:

The caluclation is on the sheet that posts to the one I want to trigger. Do I
understand you correctly that the parent sheet calucation stops the copy from
triggering. Following is the code I have it works on data entry on worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.Column = 8 And Target.Value And IsNumeric(Target.Value) Then _
Call CopyMailD(Target)
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub
Thanks

"JLatham" wrote:

Yes, use some code in the receiving worksheet's _Change() event handler to
deal with it. The Change event is triggered when a value in a cell on the
sheet changes either by user input or as the result of VB code altering a
cell's contents. I am interpreting your term "procedure" to mean the result
of some VB code action.

It does NOT fire if the change results because of a formula
calculation/recalculation in a cell.

You'd probably want to include a statement at the start of your event
handler as
Application.EnableEvents = False
and then do your processing dealing with the change(s)
and then just before exiting the event handler:
Application.EnableEvents = True

The reason for this is that each change in each cell can trigger the Change
event, and since I imagine you may be working with more than one cell at a
time, you don't want to call your event handler 50 times for a 50 cell change.

"Curt" wrote:

is there a way to have data that is copied from another worksheet by a
procedure trigger a procedure on the worksheet that receives the data. This
would be app to each row as data is entered in parent worksheet.
This community sure is a great bunch.
Thanks

worksheet
 

This is my code in parent sheet the value of trigger cell is changed here
then copied. I think this is what you mean. I will have to try to work around
this?

If Target.Column = 12 And Target.Value 10 And IsNumeric(Target.Value)
Then _
Call CopyDonors(Target)
"( If Target.Column = 12 And Target.Value 10 Then Target.Value = 10)"
If Target.Column = (12) And Target.Value <= 0 Then _
Then to other worksheet

If Target.Column = 8 And Target.Value = 0 And IsNumeric(Target.Value) Then _
Call CopyMailD(Target)

If I follow you the "( )" line is problem. Is there a change you can
recommend to overcome this? Will try moveing the location of code to see if
that works.

Thank You for the help.


"JLatham" wrote:

I believe you're good to go with that. The Application.EnableEvents = False
statement will keep the change event from triggering when you do whatever is
being done in CopyMailD(Target) gets executed.

The If statement you have will deal with the values entered into column H
and ignore changes in other cells on the sheet, ignoring text or emptied
cells (as with [Del]) that trigger the event.

"Curt" wrote:

The caluclation is on the sheet that posts to the one I want to trigger. Do I
understand you correctly that the parent sheet calucation stops the copy from
triggering. Following is the code I have it works on data entry on worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.Column = 8 And Target.Value And IsNumeric(Target.Value) Then _
Call CopyMailD(Target)
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub
Thanks

"JLatham" wrote:

Yes, use some code in the receiving worksheet's _Change() event handler to
deal with it. The Change event is triggered when a value in a cell on the
sheet changes either by user input or as the result of VB code altering a
cell's contents. I am interpreting your term "procedure" to mean the result
of some VB code action.

It does NOT fire if the change results because of a formula
calculation/recalculation in a cell.

You'd probably want to include a statement at the start of your event
handler as
Application.EnableEvents = False
and then do your processing dealing with the change(s)
and then just before exiting the event handler:
Application.EnableEvents = True

The reason for this is that each change in each cell can trigger the Change
event, and since I imagine you may be working with more than one cell at a
time, you don't want to call your event handler 50 times for a 50 cell change.

"Curt" wrote:

is there a way to have data that is copied from another worksheet by a
procedure trigger a procedure on the worksheet that receives the data. This
would be app to each row as data is entered in parent worksheet.
This community sure is a great bunch.
Thanks