VBA code slowing Workbook

[Keely]

Hi folks!

The following VBA code is applied to a single worksheet, it triggers and email send (via Outlook) when a cell in a row becomes "Overdue". It actually works fine, however I've noticed upon opening the workbook it takes an AGE to calculate and then allow me to modify the worksheets.

Do you have any idea what I can apply to speed it up? I need to ensure other code within the same workbook isn't affected.

Private Sub Worksheet_Calculate()

    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As String


    NotSentMsg = "Email Reminder Not Sent"
    SentMsg = "Email Reminder Sent"


'Above the MyLimit value it will run the macro
    MyLimit = "OVERDUE"


Sheets("Contractor Update").Select
Application.ScreenUpdating = False


'Set the range with Formulas that you want to check
    Set FormulaRange = Me.Range("R5:R105")


    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = True Then
                MyMsg = "Incorrect Stage Complete value"
            Else
                If .Value = MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 19).Value = NotSentMsg Then
                        [B]Call Mail_with_outlook2[/B]
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 19).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell


ExitMacro:
    Exit Sub


EndMacro:
    Application.EnableEvents = True


    MsgBox "Error occurred. Contact Keely" _
         & vbLf & Err.Number _
         & vbLf & Err.Description


Application.ScreenUpdating = True
         
End Sub

 

[Norie]

This code will run whenever the worksheet calculates, are you sure you want that?

Is there any other event you could use?

For example, do the cells with formulas, R5:R105, refer to cells that take manual input?

 

[Keely]

I only really need the code to run once a day, every day, but to be honest I don't know how to do this.

Also, data in R is calculated by adjacent cell values, the formula in R cells is as follows

=IF(AND(N5="",H5<=TODAY()),"OVERDUE",IF(AND(N5="",H5>=TODAY()),"DUE",IF(AND(N5<>"",H5>=N5),"ON TIME","LATE")))

(I'm sure if I had the time it could all be done in VBA - maybe one day!)

 

[Norie]

When would you want/need to run the code?

You could add a button to run it whenever you wanted to, or you could add code to run it when the workbook is closed/opened.

 

[Keely]

Ideally once a day, every day. I'm currently looking a the open/closed option

 

[SiRhOSEven]

Hi Keely, can this code work for any email? I mean not necessarily use outlook app.

 

[Keely]

Hi Keely, can this code work for any email? I mean not necessarily use outlook app.

I'm not sure, I sourced it from www.rondebruin.nl

 

[Keely]

Hi folks.
Still not able to resolve this. I've pasted the following in 'This Worksheet' code and the module is called OverduePPM yet it still doesn't run. Any idea?

Private Sub Workbook_Open()


RunFunctions_Flag = True
Application.ScreenUpdating = False
Call FeeCalculator.UpdateFees_M


Worksheets("Contractor Update").Activate
    Call OverduePPM
    
Application.ScreenUpdating = True
         
End Sub