Last part of VBA code/Msg box not coming up when workbook opened, only when specified range is refreshed

[willow1985]

The below code works with the exception of the line:

If Range("Z1").Value = "1" Then
MsgBox ("AUDIT LIST UPDATE REQUIRED")

I want the code to check cell Z1 (which is a formula) when the workbook is opened and display the message box if the value is 1, however the msg box only comes up if I refresh cell Z1.

Any suggestions?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If Target <> "" Then Call Notify
End If

If Range("Z1").Value = "1" Then
    MsgBox ("AUDIT LIST UPDATE REQUIRED")

End If
End Sub

 

[mumps]

You need to place the macro in the code module for ThisWorkbook not in the worksheet code module. Change the sheet name (in red) to suit your needs.

Private Sub Workbook_Open()
    If Sheets("Sheet1").Range("Z1").Value = "1" Then
    MsgBox ("AUDIT LIST UPDATE REQUIRED")
End Sub

 

[willow1985]

Thank you very much. Had an off moment ?

 

[mumps]

You are very welcome.