Automatic Macro when Cell Value changes

[Steven1985]

Hi, Im new to this and also a novice with excel and would really appreciate help with my query please?

I have created a spreadsheet for work to monitor supplier performance and referrals of accounts to other areas of the business. This spreadsheet is a template to copied daily for the analysts to work.

I have in Cell B2 the date of the Alert. In cell AE2 I have the formula -today() to calculate todays date. In cell AF2 i have in the cell to monitor the Service Levels (the difference in days from Cell B2 - AE2) and the formula used in AF2 is =DATEDIF(B2, AE2, "D"). So far all this works.

What I need to do is have a macro that will run automatically when cell AF2 reaches 3 days SLA to then send an e-mail to an Analyst to promt them to chase up the referral.

Can someone please help with this? Any further information required im happy to provide. Many Thanks. Steve

 

[Steven1985]

I have done this but nothing is happening, im obviously doing somthing wrong.

 

[VoG]

Have you done something to trigger a calculation, like pressing F9 or changing one of the precedent cells.

 

[Steven1985]

Keybored Shortcut: ctrl + e

Private Sub Worksheet_Calculate()
If Range("AF2").Value >= 3 And Range("AG2").Value = "" Then
Range("AG2").Value = "Yes"
Call SLA
End If
Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there" & vbNewLine & vbNewLine & _
"Cell AF2 is changed" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
On Error Resume Next
With OutMail
.To = "steven@work.com"
.CC = ""
.BCC = ""
.Subject = "Referral over SLA"
.Body = strbody
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Thats all of the text I have added onto VBA.

Sorry, Im not used to working excel this advanced.

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Insert Module and paste in

Sub SLA()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Hi there" & vbNewLine & vbNewLine & _
"Cell AF2 is changed" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
On Error Resume Next
With OutMail
    .To = "steven@work.com"
    .CC = ""
    .BCC = ""
    .Subject = "Referral over SLA"
    .Body = strbody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Note that I changed the name of the macro to SLA. You will need to change steven@work.com to a real e-mail address.

Press ALT + Q to exit the code window. Press ALT + F8, click on SLA and click the Run button. This should fire an e-mail. If it does go back to your sheet, Press F9 to force a calculation and see if that triggers an e-mail.

 

[Steven1985]

Thats brilliant it works!! Thanks!

How can I know ensure it works automatically, do i need to add the text below to the module of the e-mail or onto a seperate module?

Private Sub Worksheet_Calculate()
If Range("AF2").Value >= 3 And Range("AG2").Value = "" Then
Range("AG2").Value = "X"
Call SLA
End If
End Sub

 

[VoG]

That code has to go in the worksheet's code module. Right click the worksheet's tab, select View Code and paste in the code.

 

[Steven1985]

Im almost there, im trying to add in the text highlighted Red but now the VBA is highlighting it Yellow? What have I done now?

Previous Code

Private Sub Worksheet_Calculate()
If Range("AF2").Value >= 3 And Range("AG2").Value = "" Then
Range("AG2").Value = "Yes"
Call SLA
End If
End Sub

New Code

Private Sub Worksheet_Calculate()
If Range("AF2:AF1000").Value >= 3 And Range("AG2:AG1000").Value = "" Then
Range("AG2:AG1000").Value = "Yes"
Call SLA
End If
End Sub

 

[VoG]

You need to loop through the range

Private Sub Worksheet_Calculate()
Dim cell As Range
For Each cell In Range("AF2:AF1000")
    If cell.Value >= 3 And cell.Offset(, 1).Value = "" Then
        cell.Offset(, 1).Value = "Yes"
        Call SLA
    End If
Next cell
End Sub

 

[Steven1985]

It's working now. Thanks Pete, I really do appreciate your help. You have been fantastic.
Cheers!!

 

[Steven1985]

Hi Peter,

Thanks for your help. I do however have another query regarding this. I currently have it all set up and running fine. I now need to fine tune it. I have a list of e.g 100 alerts. 50 alerts are referred to different areas of the business.

I have this macro currently set up on one tab 'Alert Spreadsheet'. I have 4 other tabs, 'A, B, C, D' to record the referrals. It would be easier to record the SLA's on the other 4 tabs rather than the main spreadsheet. Any idea how i can do this?

As it stands all the alerts are being recoreded as over SLA when in the example used above on 50 cases need to be brought to our attention.

Hope this makes sense?