First of all, you're disabling the events, then never re-enabling them if the
value is not "yes".
I would first test to see if the changed cell is in the target range,
then test the value, THEN disable the events, make the changes,
then re-enable the events.
If you change a cell NOT in column "C", then you're issuing the message.
I would think it's ok to make changes to the task and person columns.
I would modify it like:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If (Not Intersect(Range(Target.Address), Range("c1:c100")) Is Nothing) Then
If (UCase(Target.Value) = "YES") Then
Application.EnableEvents = False
macrodef
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
Else: MsgBox "Please leave blank if not complete"
End If
End If
End Sub
'-----------------------------------------------
Sub macrodef()
Cells.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows(ActiveCell.Row).Select
Selection.Cut
Sheets("Completed").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Active").Select
Selection.Delete Shift:=xlUp
Cells(Selection.Row, 3).Select
MsgBox "Task was moved to completed tab"
End Sub
________________________________
From: DEF <defl...@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Sent: Fri, June 25, 2010 12:38:45 PM
Subject: $$Excel-Macros$$ Cell entry activates code to move line to diff tab
So I have a file where a task/person responsible etc. is listed
When the task is completed, I would like to enter yes and upon this
text entry, the file would delete that line and add it to another tab
with other completed tasks. I need other entries of text to have
nothing done.
Task Person Complete?
RFP Jane Doe
The below code kind of works but when anything other than "yes" is
entered in column C, the sheet event fails to activate. Help please:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Intersect(Target, Range("c1:c100")) = "yes" Then
macrodef
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error Resume Next
Else: MsgBox "Please leave blank if not complete"
End If
End Sub
Sub macrodef()
'
' macrodef Macro
Cells.Find(What:="yes", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
Rows(ActiveCell.Row).Select
Selection.Cut
Sheets("Completed").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Sheets("Active").Select
Selection.Delete Shift:=xlUp
MsgBox "Task was moved to completed tab"
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
