MACRO to pop-up alert if value of cell changes

[martenian]

Hi All,

I'm looking for help with creating a macro to pop-up an alert message if the value of cell "S8" on "Actual vs Plan" tab changes. Ideally i'd also like the message to display the change between the original value and the new value of cell "S8".

Something like this:

IF value of cell S8 <> then call alert message "The EAC Unbilled has changed by "X". Please update the reviewer tab to record this change."

Going a step further, it would be awesome if the macro could also post the new value from cell "S8" to the "Reviewer" tab in the next blank cell in column J (range J6:J61).

I have the following columns on the reviewer tab:
Description of Changes.....Time Period covered .....Task owner Initial....Date.....EAC Unbilled

I would really like to have a text field show in the pop-up message to input a "Description" (F:F) for the change in value of cell "S8". I'd like for the "Time Period Covered" (G:G) to be a from-to date field. The "Task Owner Initial" (H:H) to be a select list, and the "date" (I:I) to be auto populated with today's date.

I know this is asking a lot so any suggestions would be great. This is my first time posting to the forums so i apologize if my question is hard to follow.

I've tried a couple of things on my own but cant quite get it to work correctly. Any and all suggestions welcome.

 

[Michael M]

Don't have Excel at the moment, but copy this into the Actual vs Plan Sheet module

Sub worksheet_change(ByVal target As Range)
 Dim lr As Long
 If Not Intersect(target, target.Range("S8")) Is Nothing Then Exit Sub
 MsgBox "The EAC Unbilled has changed to " & Range("S8").Value & ""
lr = Sheets("Reviewer").Cells(Rows.Count, "J").End(xlUp).Row
Application.EnableEvents = False
Range("S8").Copy Sheets("Reviewer").Range("J" & lr + 1)
Application.EnableEvents = True

End Sub

 

[Michael M]

Hmmm....didn't see the "amount changed" requirement....try this

Sub worksheet_change(ByVal target As Range)
 Dim lr As Long, n As Variant
 If Not Intersect(target, target.Range("S8")) Is Nothing Then Exit Sub
lr = Sheets("Reviewer").Cells(Rows.Count, "J").End(xlUp).Row
n = Range("S8").Value - Sheets("Reviewer").Range("J" & lr).Value
 MsgBox "The EAC Unbilled has changed by " & n & ""
Application.EnableEvents = False
Range("S8").Copy Sheets("Reviewer").Range("J" & lr + 1)
Application.EnableEvents = True
End Sub

 

[martenian]

Hi Michael. Thanks a lot for your response. I just tried everything out by copying the code into the AVP sheet and for some reason it is trying to copy the value from "S8" on another sheet. Also, the changed by value in the pop-up message is showing 0. I'm assuming this is because "S8" on the tab that it is trying to copy from is blank. Aside from that everything seems to be working great. Any suggestions on what might be causing that?

 

[Michael M]

The AVP sheet is the one you want to refer to isn't it ??
I just recreated the 2 sheets, posted the code into the AVP sheet module, made sure there were no codes in the Reviewer sheet module and made changes to S* in the AVP sheet.......it worked fine for me !!

Can you upload to dropbox so I can take a look ??