useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

A very good question. The event code needs to remember the previous value of
some variable.

Declare a static variable in a standard module above any subs or functions
in that module. The event code can reference that variable and update its
value as necessary.
--
Gary''s Student
gsnu200710


"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

If Range("B1") 10 Then
'run your code here
Range("B1").Value = 10
End If

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

thanks much see how we do will print and compare to see if I can understand
Learning is fun
Thanks Again "Tom"

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

set rng error requires object not familar with this. code is option explict
Thanks

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

took care of error problem. Have a conflict with copydonors. When code in
place copy donors does not copy as before. Also code when you hit enter on
target cell returns you to next line and column 'B'

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

Curt,
without having the spread sheet in front of me and knowing what you are
trying to do, the best I can do is try to give you some ideas. That is what
I attempted to do.

--
Regards,
Tom Ogilvy






"Curt" wrote:

took care of error problem. Have a conflict with copydonors. When code in
place copy donors does not copy as before. Also code when you hit enter on
target cell returns you to next line and column 'B'

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

You do a good job with out all the facts will try to include spread sheet
I sure do appreciate all of your help Will not let me paste spreadsheet so
you can see. Data sheet has all code in sheetchange event. when it runs copy
donors it pastes into donors sheet needed info then it sets target to 10. all
sheets are in same workbook
Is there a way to send spreadsheet? Will try to modify as best this guy can
Thanks Much




"Tom Ogilvy" wrote:

Curt,
without having the spread sheet in front of me and knowing what you are
trying to do, the best I can do is try to give you some ideas. That is what
I attempted to do.

--
Regards,
Tom Ogilvy






"Curt" wrote:

took care of error problem. Have a conflict with copydonors. When code in
place copy donors does not copy as before. Also code when you hit enter on
target cell returns you to next line and column 'B'

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and change
trigger value trigger. value is 10 want to change it to 10 after runnung
procedure. can we do this?
Thanks

Not to the group.



if you want me to look at it.



--
Regards,
Tom Ogilvy

"Curt" wrote in message
...
You do a good job with out all the facts will try to include spread sheet
I sure do appreciate all of your help Will not let me paste spreadsheet so
you can see. Data sheet has all code in sheetchange event. when it runs
copy
donors it pastes into donors sheet needed info then it sets target to 10.
all
sheets are in same workbook
Is there a way to send spreadsheet? Will try to modify as best this guy
can
Thanks Much




"Tom Ogilvy" wrote:

Curt,
without having the spread sheet in front of me and knowing what you are
trying to do, the best I can do is try to give you some ideas. That is
what
I attempted to do.

--
Regards,
Tom Ogilvy






"Curt" wrote:

took care of error problem. Have a conflict with copydonors. When code
in
place copy donors does not copy as before. Also code when you hit enter
on
target cell returns you to next line and column 'B'

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
If Target.column = 12 and Target.row 1 then
set rng = Range(Range("L1"),Target)
if Application.Countblank(rng) 0 then
msgbox "Don't leave any blank cells
target.clearcontents
target.end(xlup).offset(1,0).Select
Application.EnableEvents = True
exit sub
end if

If Target.Column = 12 And _
Target.Value 10 And _
IsNumeric(Target.Value) Then
Call CopyDonors(Target)
Target.Value = 10 '< change the value after
calling
the sub
elseif Target.Column = 12 And _
Target.Value <= 0 Then
Call Copycomp(Target)
end if
end if
Application.EnableEvents = True
Exit Sub
errhandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Curt" wrote:

useing event to trigger then after a code is run to come back and
change
trigger value trigger. value is 10 want to change it to 10 after
runnung
procedure. can we do this?
Thanks