Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically appending newly added data on worksheet to a master list worksheet | Links and Linking in Excel | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Discussion (Misc queries) | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | New Users to Excel | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
Attaching a JET database to an Excel Worksheet OR storing large binary data in a worksheet | Excel Programming |