Event macro to move rows based on Column

[onbeillp111]

Hey everyone,

I have a tracking workbook that tracks open items, when an item becomes closed, the user selects the "Status" Column and chooses closed. When closed is selected, a window pops up with confirmation of the selection, and then it inserts the date in the next column and moves the row to the Closed tab in the workbook and deletes the row from Open tab of the workbook. This all works great. Now I need the code to have another which will essentially do the same thing. The user can either choose either to Close the item or monitor the item, then it will be moved to either the "Closed" tab or the "Monitoring" tab. I used the exact same event code and changed a few things but when "Monitor" is selected, nothing happens. The code is below and an example workbook is attached with the code. The first event works, the second event does nothing. Any help would be great.

Please Login or Register  to view this content.

[lokicl]

Hi Onbeillp111,

You can't build your own event. Only defined events works. So, you should have updated the code within the same event.
Please find below the modified code below
___________________________
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False


If Target.Column = 15 Then


If Target = "Closed" Then
Dim nResult As Long
nResult = MsgBox( _
Prompt:="Is this Item Closed?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
Exit Sub
End If

Target.Offset(0, 1).Value = Now

nxtRw = Sheets("Closed").Range("A" & Rows.Count).End(xlUp).Row + 1

Target.EntireRow.Copy Destination:=Sheets("Closed").Range("A" & nxtRw)

Target.EntireRow.Delete shift:=xlUp
End If

If Target = "Monitor" Then
Dim nResult1 As Long
nResult1 = MsgBox( _
Prompt:="Monitor this Item?", _
Buttons:=vbYesNo)
If nResult1 = vbNo Then
Exit Sub
End If

Target.Offset(0, 1).Value = Now

nxtRw = Sheets("Monitoring").Range("A" & Rows.Count).End(xlUp).Row + 1

Target.EntireRow.Copy Destination:=Sheets("Monitoring").Range("A" & nxtRw)

Target.EntireRow.Delete shift:=xlUp

End If


End If

Application.EnableEvents = True

End Sub
____________________________________

enjoy maadi!!

Regards,
Loki

If you have issues with Code I've provided, I appreciate your feedback.

In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

[tehneXus]

or this:

Please Login or Register  to view this content.

@ lokicl: Please use CODE tags

[onbeillp111]

Thanks lokicl, that worked

[JOHN H. DAVIS]

Ditto comments in Post#2

Another:

Please Login or Register  to view this content.

BTW I set your validation list source here: =$R$1:$R$3