event fire

[Guest]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Guest]

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

 

[Guest]

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

 

[kounoike]

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

 

[Guest]

Will try and see what we do let you know how it works out
Thanks You so Much

 

[Guest]

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

 

[Guest]

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

 

[Carl Hartness]

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

 

[kounoike]

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

 

[Guest]

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

 

[Tom Ogilvy]

? "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.

 

[Guest]

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]

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.

 

[kounoike]

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

 

[Guest]

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

 

[Guest]

made change as my last post now I get one more column than I want. Can you
advise what I missed?
Thanks

 

[Guest]

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

 

[Guest]

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]

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