VBA Stop Workbook From Closing Unless Data Is Filled Complete

[MATRIXOFFICE]

file size exceed forum limit so I will email thanks Rick

[Rick Rothstein]

To keep this thread updated...

I received the file from MATRIXOFFICE and found that the cells in Columns M thru O had formulas in the, so my use of WorksheetFunction.CountA was not working. I sent the following code to MATRIXOFFICE which worked in my tests, so I expect it is the code that he will end up using...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim Mnths As Long, Rws As Long, BlankCellCount As Long, WS As Worksheet
  For Mnths = 1 To 12
    Set WS = Worksheets(UCase(Format(28 * Mnths, "mmm")))
    For Rws = 5 To 150
      If Len(WS.Cells(Rws, "G").Value) Then
        BlankCellCount = WorksheetFunction.CountIf(WS.Cells(Rws, "M").Resize(, 3), "")
        If BlankCellCount Then
          Cancel = True
          MsgBox "Please check Row #" & Rws & " on sheet """ & WS.Name & """" & _
                 vbLf & vbLf & "You have an incident filled in Column G for that row " & _
                 "but you are missing one or more pieces of data in Columns M thru O"
          WS.Activate
          Cells(Rws, "M").Resize(, 3).Select
          Exit Sub
        End If
      End If
    Next
  Next
End Sub

[MATRIXOFFICE]

Rick thanks it works a treat!