Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
I am missing something and am stumped. Have the following code in the
workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
studying your code it would seem I don't need copystuff copying could be done
within the code you wrote? I lack knowledge to understand the last about Doners worksheet in your code. Hope what I sent clears the water Thank you "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
I modified your code a little and I don't know this is what you want, but
try this. Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Select Case sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then Call CopyStuff(sh, target) End If End Select End Sub Public Sub CopyStuff(ByVal sh As Worksheet, ByVal target As Range) With Sheets("Donors") trow = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row If trow < .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row Then trow = .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row End If ..Cells(trow, "E").Resize(1, 7).Value = _ sh.Cells(target.Row, "E").Resize(1, 7).Value ..Cells(trow, "J").Value = .Cells(trow, "J").Value - 10 End With End Sub keizi "Curt" wrote in message ... What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
Will try and see what we do let you know how it works out
Thanks You so Much "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this. Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Select Case sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then Call CopyStuff(sh, target) End If End Select End Sub Public Sub CopyStuff(ByVal sh As Worksheet, ByVal target As Range) With Sheets("Donors") trow = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row If trow < .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row Then trow = .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row End If ..Cells(trow, "E").Resize(1, 7).Value = _ sh.Cells(target.Row, "E").Resize(1, 7).Value ..Cells(trow, "J").Value = .Cells(trow, "J").Value - 10 End With End Sub keizi "Curt" wrote in message ... What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
for some reason I am getting type mismatch on this line? runtime error'13'
If target.Column = 10 And target.Value 10 Then any Ideas Thanks "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this. Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Select Case sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then Call CopyStuff(sh, target) End If End Select End Sub Public Sub CopyStuff(ByVal sh As Worksheet, ByVal target As Range) With Sheets("Donors") trow = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row If trow < .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row Then trow = .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row End If ..Cells(trow, "E").Resize(1, 7).Value = _ sh.Cells(target.Row, "E").Resize(1, 7).Value ..Cells(trow, "J").Value = .Cells(trow, "J").Value - 10 End With End Sub keizi "Curt" wrote in message ... What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
wonderful code put data where wanted but not wanted data. target column on
all sheets is 10 or 'J' we subtract 10.00 from this cell then copy data from cells E-F-G-H-I-J(-10.00)-K To Donors worksheet Column 'J' is formated to currency other cells are data Hope I make sense. Thank You "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
Hi Curt,
CopyStuff has a couple of options, with and without target.value. Delete the one you don't want. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub Public Sub CopyStuff(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(1, 0) ' recommend disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False ' option 1, if you want value from col J in column A? target.Copy Destination:=rngPaste Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste.Offset(0, 1) rngPaste.Offset(0, 6) = target - 10 ' option 2, next row down for demo purposes Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -5), target.Offset(0, 2)) Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste rngPaste.Offset(0, 5) = target - 10 Application.EnableEvents = True End Sub Carl On Feb 28, 11:33 pm, Curt wrote: What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
It works for me without error, so I have no idea about it. but my code fires
event three times in series, it's not good , so my guess is that target is supposed to be a single cell but somewhere target has changed to be cells and has failed to get target.value. if you show me the code you tried, then i'll try to check it. keizi "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If.Column = 10 And target.Value 10 Then any Ideas Thanks "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
I will play with this and see if I can do it. If not I will be back. Not real
good at this. No data goes into same column cell in Donors wks as it comes from Data wks. Anyway I am going to do my best. Thanks for your generosity in helping "Carl Hartness" wrote: Hi Curt, CopyStuff has a couple of options, with and without target.value. Delete the one you don't want. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub Public Sub CopyStuff(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(1, 0) ' recommend disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False ' option 1, if you want value from col J in column A? target.Copy Destination:=rngPaste Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste.Offset(0, 1) rngPaste.Offset(0, 6) = target - 10 ' option 2, next row down for demo purposes Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -5), target.Offset(0, 2)) Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste rngPaste.Offset(0, 5) = target - 10 Application.EnableEvents = True End Sub Carl On Feb 28, 11:33 pm, Curt wrote: What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
? "A" 10
gives me type mismatch error in the immediate window. Maybe target contains text or something that can't be compared to a number. -- Regards, Tom Ogilvy "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If target.Column = 10 And target.Value 10 Then any Ideas Thanks "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this. Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Select Case sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then Call CopyStuff(sh, target) End If End Select End Sub Public Sub CopyStuff(ByVal sh As Worksheet, ByVal target As Range) With Sheets("Donors") trow = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row If trow < .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row Then trow = .Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row End If ..Cells(trow, "E").Resize(1, 7).Value = _ sh.Cells(target.Row, "E").Resize(1, 7).Value ..Cells(trow, "J").Value = .Cells(trow, "J").Value - 10 End With End Sub keizi "Curt" wrote in message ... What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
Not good at explain I know what I want but to try to convey in words get
complex. We are together about trigger from cell in column 'J' If dollar amount in'J' is over 10.00 then copy data e to donors a data f don b data g don c data h to don d data I to don e data j less 10.00 to don f data k don G dont know how i got the A column mixed in my thoughts. Hope this makes sense Thanks Much "Carl Hartness" wrote: Hi Curt, CopyStuff has a couple of options, with and without target.value. Delete the one you don't want. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub Public Sub CopyStuff(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(1, 0) ' recommend disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False ' option 1, if you want value from col J in column A? target.Copy Destination:=rngPaste Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste.Offset(0, 1) rngPaste.Offset(0, 6) = target - 10 ' option 2, next row down for demo purposes Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -5), target.Offset(0, 2)) Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste rngPaste.Offset(0, 5) = target - 10 Application.EnableEvents = True End Sub Carl On Feb 28, 11:33 pm, Curt wrote: What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
The event handler fires three times, first for the change that starts
the process on the sheet with the target, the second and third times when copied data changes the Donors sheet. Setting EnableEvents to False before and to True after the copy/paste steps blocks that behavior. The alternative is to put the SheetChange code only in the class modules of the sheets where it is wanted, such as Motorcycle, Indian, etc Carl. On Mar 1, 6:27 pm, "kounoike" wrote: It works for me without error, so I have no idea about it. but my code fires event three times in series, it's not good , so my guess is that target is supposed to be a single cell but somewhere target has changed to be cells and has failed to get target.value. if you show me the code you tried, then i'll try to check it. keizi "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If.Column = 10 And target.Value 10 Then any Ideas Thanks "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this.- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
According to Tom and Cart's suggestions, I correct the code in
Workbook_SheetChange a little. Thanks a lot for both of your suggestions. Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range) On Error GoTo errhandler Application.EnableEvents = False Select Case sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And _ target.Value 10 And IsNumeric(target.Value) Then Call CopyStuff(sh, target) End If End Select Application.EnableEvents = True Exit Sub errhandler: Application.EnableEvents = True End Sub keizi "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If target.Column = 10 And target.Value 10 Then any Ideas Thanks |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
option_2 did the trick Now I find out I must move data in column J that is
trigger over (2) columns If I follow right this would change code offset from (0,-5) to (0,-7) also mgPaste from (0, 5) to (0,7) Not sure would all other offsets remain same? will be actually adding a column marked my change in code below*()*option_2 Sure do appreciate your assistance. Thanks Again "Carl Hartness" wrote: Hi Curt, CopyStuff has a couple of options, with and without target.value. Delete the one you don't want. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub Public Sub CopyStuff(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(1, 0) ' recommend disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False ' option 1, if you want value from col J in column A? target.Copy Destination:=rngPaste Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste.Offset(0, 1) rngPaste.Offset(0, 6) = target - 10 ' option 2, next row down for demo purposes Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -*(7)(5)*, target.Offset(0, 2)) Range(target.Offset(0, -*(7)(5)*, target.Offset(0, 2)).Copy Destination:=rngPaste rngPaste.Offset(0,*(7)(5)* = target - 10 Application.EnableEvents = True End Sub Carl On Feb 28, 11:33 pm, Curt wrote: What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
made change as my last post now I get one more column than I want. Can you
advise what I missed? Thanks "Carl Hartness" wrote: Hi Curt, CopyStuff has a couple of options, with and without target.value. Delete the one you don't want. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub Public Sub CopyStuff(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(1, 0) ' recommend disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False ' option 1, if you want value from col J in column A? target.Copy Destination:=rngPaste Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste.Offset(0, 1) rngPaste.Offset(0, 6) = target - 10 ' option 2, next row down for demo purposes Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -5), target.Offset(0, 2)) Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste rngPaste.Offset(0, 5) = target - 10 Application.EnableEvents = True End Sub Carl On Feb 28, 11:33 pm, Curt wrote: What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub- Hide quoted text - - Show quoted text - |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
event works with minor problem here is code as I changed it. Problem is I
copy one column more than I want on paste. Also it skips a line when pasting. Any ideas. I've went in and changed all I can think of. Trigger has been moved to column (L) Also is there a way to prevent reentering same data or changing on first worksheet. If you chg trigger data it will reenter the line. Can this be locked so if user changes the trigger data it will change only that entry on Donors sheet Not add a new line. Or maybe I should use a different approach to solve this. This is a first go around for this guy at something this complex. Appreciate Greatly your assistance. So will those who use this. Thanks Again Public Sub CopyStuff(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(1, 0) Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -7), target.Offset(0,2)) Range(target.Offset(0, -7), target.Offset(0, 2)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub "Carl Hartness" wrote: The event handler fires three times, first for the change that starts the process on the sheet with the target, the second and third times when copied data changes the Donors sheet. Setting EnableEvents to False before and to True after the copy/paste steps blocks that behavior. The alternative is to put the SheetChange code only in the class modules of the sheets where it is wanted, such as Motorcycle, Indian, etc Carl. On Mar 1, 6:27 pm, "kounoike" wrote: It works for me without error, so I have no idea about it. but my code fires event three times in series, it's not good , so my guess is that target is supposed to be a single cell but somewhere target has changed to be cells and has failed to get target.value. if you show me the code you tried, then i'll try to check it. keizi "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If.Column = 10 And target.Value 10 Then any Ideas Thanks "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this.- Hide quoted text - - Show quoted text - |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
I noted this in code target.Value 10 And IsNumeric(target.Value) Useing
this could a call be made if Target was blank. Need to keep from having repeated entries of same data. This can happen if a person changes trigger data on Data input worksheet Havent figured that one out either. "kounoike" wrote: According to Tom and Cart's suggestions, I correct the code in Workbook_SheetChange a little. Thanks a lot for both of your suggestions. Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range) On Error GoTo errhandler Application.EnableEvents = False Select Case sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And _ target.Value 10 And IsNumeric(target.Value) Then Call CopyStuff(sh, target) End If End Select Application.EnableEvents = True Exit Sub errhandler: Application.EnableEvents = True End Sub keizi "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If target.Column = 10 And target.Value 10 Then any Ideas Thanks |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
I'm not quite sure that i'm following you, i rewrite your code like this.
Public Sub CopyStuff(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(1, 0) If target.Font.ColorIndex < 3 Then target.Font.ColorIndex = 3 Else Exit Sub End If Application.EnableEvents = False Set rngPaste = rngPaste.Resize(1, 10) Set target = Range(target.Offset(0, -7), target.Offset(0, 2)) rngPaste.Value = target.Value rngPaste.Cells(1, 8) = rngPaste.Cells(1, 8) - 10 Application.EnableEvents = True End Sub keizi "Curt" wrote in message ... event works with minor problem here is code as I changed it. Problem is I copy one column more than I want on paste. Also it skips a line when pasting. Any ideas. I've went in and changed all I can think of. Trigger has been moved to column (L) Also is there a way to prevent reentering same data or changing on first worksheet. If you chg trigger data it will reenter the line. Can this be locked so if user changes the trigger data it will change only that entry on Donors sheet Not add a new line. Or maybe I should use a different approach to solve this. This is a first go around for this guy at something this complex. Appreciate Greatly your assistance. So will those who use this. Thanks Again Public Sub CopyStuff(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(1, 0) Application.EnableEvents = False Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -7), target.Offset(0,2)) Range(target.Offset(0, -7), target.Offset(0, 2)).Copy _ Destination:=rngPaste rngPaste.Offset(0, 7) = target - 10 Application.EnableEvents = True End Sub "Carl Hartness" wrote: The event handler fires three times, first for the change that starts the process on the sheet with the target, the second and third times when copied data changes the Donors sheet. Setting EnableEvents to False before and to True after the copy/paste steps blocks that behavior. The alternative is to put the SheetChange code only in the class modules of the sheets where it is wanted, such as Motorcycle, Indian, etc Carl. On Mar 1, 6:27 pm, "kounoike" wrote: It works for me without error, so I have no idea about it. but my code fires event three times in series, it's not good , so my guess is that target is supposed to be a single cell but somewhere target has changed to be cells and has failed to get target.value. if you show me the code you tried, then i'll try to check it. keizi "Curt" wrote in message ... for some reason I am getting type mismatch on this line? runtime error'13' If.Column = 10 And target.Value 10 Then any Ideas Thanks "kounoike" wrote: I modified your code a little and I don't know this is what you want, but try this.- Hide quoted text - - Show quoted text - |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
event fire
Thank You for my starting.
This old dog got it to do just what I wanted finnaly You sure were a big help. I wonder if there is a way to have more ifs so each entry could be posted to other sheet after enter on data sheet. option 2 was a suberb code Thanks again "Carl Hartness" wrote: Hi Curt, CopyStuff has a couple of options, with and without target.value. Delete the one you don't want. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub Public Sub CopyStuff(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(1, 0) ' recommend disabling events to block extra passes through ' Workbook_SheetChange caused by changing Donors cells Application.EnableEvents = False ' option 1, if you want value from col J in column A? target.Copy Destination:=rngPaste Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste.Offset(0, 1) rngPaste.Offset(0, 6) = target - 10 ' option 2, next row down for demo purposes Set rngPaste = rngPaste.Offset(1, 0) rngPaste = Range(target.Offset(0, -5), target.Offset(0, 2)) Range(target.Offset(0, -5), target.Offset(0, 2)).Copy Destination:=rngPaste rngPaste.Offset(0, 5) = target - 10 Application.EnableEvents = True End Sub Carl On Feb 28, 11:33 pm, Curt wrote: What I am trying to do is a workbookevent. It is to fire when entry in column 'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells 'E' 'F''G''H''I 'J-10.00' K'&'L of same row to next open row in donors worksheet or it may insert a row. I have the first row on all sheets frozen for scrolling reasons and labels. I am suprised that I've got this far. Now when entry is made error workbook sub in yellow select case in blue in my code will insert my code here Thanks this old dog learning new tricks Resources as you are a Blessing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) SelectCase Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs If target.Column = 10 And target.Value 10 Then _ Call .CopyStuff(target) End Select End Sub I know the offsets are not right in this not sure of the rows count etc. Public Sub CopyStuff(ByVal target As Range) Dim wksSummary As Worksheet Dim rngPaste As Range Set wksSummary = Sheets("Donors") Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) rngPaste = target.Value rngPaste.Offset(0, 1) = target.Offset(0, -1) End Sub Finally Thanks Again "Jim Cone" wrote: Your explanation was not clear to me. I've found that if you can describe exactly what you want to do then writing the code becomes much easier. Here is my interpretation. The code goes in the ThisWorkbook module. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) Dim dblValue As Double Dim varTargetAmt As Variant varTargetAmt = Target.Cells(1, 1).Value Select Case Sh.Name Case "Data" If Target.Column = 1 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Motorcycle" If Target.Column = 6 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Indian" If Target.Column = 7 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If Case "Native" If Target.Column = 8 And varTargetAmt 10 Then dblValue = varTargetAmt - 10 End If End Select If dblValue 0 Then Me.Worksheets("doner").Range("E2:I2").Value = _ Array(dblValue, 10, 20, 30, 40) End If End Sub '----------- "Curt" wrote in message I am missing something and am stumped. Have the following code in the workbook I want it to run when an entry is made in column 'J' or '10' reference Then subtract 10.00 amd take the remaining amount and place this and other data in row in columns 'E F G H I' into donors worksheet. This is my first attempt at this event happening. Anyway here is code Any takers? Thanks Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=Data 6=Motorcycle 7=Indian 8=Native If target.Column = 10 And target.Value 10 Then Call _ CopyStuff(target) End Select End Sub- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get Sheet_Change event to fire - please help | Excel Programming | |||
Validation list changes don't fire an event | Excel Programming | |||
Event doesn't fire | Excel Discussion (Misc queries) | |||
Workbook Open Event does not fire | Excel Programming | |||
Event class doesn't fire in embedded VBA | Excel Programming |