What is wrong with my Code

[wheels70]

HI Guys!

i would really appreciate some help. Basically, I have written this VBA code.

What it should do is: On close check sheets 2 - 30, if O8 has data and O4 doesnt have data, then prompt the user to enter required field.

For some reason i cant get it to work!

Code below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call CheckField
Cancel = True
End Sub
Sub CheckField()
Application.ScreenUpdating = False
For Each Worksheet In ActiveWorkbook.Worksheets
If Worksheet.Range("O8").Value <> Not Empty And Worksheet.Range("O4").Value <> Empty Then
MsgBox "Please Fill in Required Fields Fields:" _
, vbOKOnly, "Missing Information Required"
Cancel = True
End If
Application.EnableEvents = False
Next
End Sub

[Bob Phillips]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)    Cancel = CheckField
End Sub


Function CheckField() As Boolean
Dim ws As Worksheet


    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    For Each ws In ActiveWorkbook.Worksheets
    
        If ws.Range("O8").Value = "" And ws.Range("O4").Value = "" Then
    
            MsgBox "Please Fill in Required Fields Fields on sheet " & ws.Name & ":", _
                   vbOKOnly, _
                   "Missing Information Required"
            CheckField = True
            Exit For
        End If
    Next
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Function