Fix My Macro for Me, Please!

[poetic]

Hey there.

I have a database that is configured to reflect when a date for a list of agreements expire. How it works is that when the date of expiry for an agreement has passed, in the column next to the dates (column M) there is an automated change from 'No' to 'Yes' to inform me that the agreement now needs renewal. I am able to send the workbook subsequent to this to all my partners and peers involved as a matter of notification, and this method works just fine. The thing is though that, unless i remember to check the file ever so often, the dates might pass a while before i am able to notify the relevant persons. I would wish to be able to immediately notify them and so want the process of automating my send mail function every time the text changes in Column M from 'No' to 'Yes', indicating expiry.

The macro i have sought to use is this one, but it doesn't work and i'm hoping you guys can fix it and tell me why. Cheers. Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = False
If Not Intersect(Target, Range("M3:M2500")) Is Nothing Then
iLastRow = Cells(Rows.Count, "M").End(xlUp).Row ''''Find Last Row
Dim Rng As Range
For i = iLastRow To 1 Step -1 '''''Start Looking From Bottom Up
If LCase(Cells(i, "M").Value) = "canceled" Then ''Check If Cell M? is equal to 'Yes'"
MsgBox "This code works" 'Call Sub SendMAil()'
End If
Next i
End If
Application.ScreenUpdating = True

 

[JP2112]

poetic,

Please use code tags:

[CODE]your code goes here[/CODE]

Also, please use a meaningful subject line. "Fix My Macro for me" isn't going to inspire many people to read your post. It doesn't describe the issue, and basically is asking for others to do your work for you to boot.

Regarding your code, as it stands you need to open the relevant workbook and make a change on the worksheet before the event handler will fire. So it won't know that a certain date has passed until those two things happen. I'm afraid there's no way around that, unless you want to set up a task scheduler to open the workbook periodically.

A better place for the code would be the Workbook_Open event, so you could simply open the workbook and it would start doing its work.

Private Sub Workbook_Open()
Dim iLastRow As Long
Dim i As Long
  Application.ScreenUpdating = False
 
    iLastRow = cells(rows.count, "M").End(xlUp).row  ''''Find Last Row
    Dim Rng As range
    For i = iLastRow To 1 Step -1  '''''Start Looking From Bottom Up
      If cells(i, "M").value = "Yes" Then  ''Check If Cell M? is equal to 'Yes'"
        Call SendMail
      End If
    Next i
  Application.ScreenUpdating = True
 
End Sub

This code would be placed in the ThisWorkbook module, instead of the sheet module where the previous code was placed.

 

[poetic]

Thank you for your advice re posting JP -- all is appreciated and duly noted. Thanks also for responding to my post. I am sorry if i gave the impression of wanting persons to do my work for me; this was not my intention. I am most certainly a noob and so don't understand very much in the way of VBA and macros. Again, my apologies.

I tried your code and it didn't work. Initially i thought it was maybe because i have a splash screen that uses the opejn workbook code for a userform but after deleting the code and trying your code alone as the open workbook event, it gave me an error which said "Compile error: Argument not optional". Could you please say how i could rectify this? Thanks for your help...looking forward to your response.

 

[JP2112]

Looks like SendMail is causing the error. You have to call the method with parameters. In the VB Editor, click the word "SendMail" and press F1 to get a list of parameters.

HTH