VBA Stop Workbook From Closing Unless Data Is Filled Complete

[MATRIXOFFICE]

Bump## anymore t houghts on this Rick?

[Rick Rothstein]

Bump## anymore t houghts on this Rick?

Sorry about my absence. See if this code will do what you want...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim Mnths As Long, Rws As Long, FilledCellCount 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
        FilledCellCount = WorksheetFunction.CountA(WS.Cells(Rws, "M").Resize(, 3))
        If FilledCellCount <> 3 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]

no probs Rick will try it out

[MATRIXOFFICE]

NO good rick can I send you the actual file and you can look at it?

[Rick Rothstein]

NO good rick can I send you the actual file and you can look at it?

Before you do that, describe "no good" for me so I have an idea what the code does or does not do correctly.

Note: I'll be going to sleep soon, so if you respond and do not hear right back from me, you will know why.

[MATRIXOFFICE]

thanks RIck - code is installed in this workbook but does not work at all - data in incident cell with no data in M N and O cells does not trigger
the code at all

[Rick Rothstein]

Okay, I'll look at your workbook. You should be able to attach your workbook to your reply (a direct reply, not a quick reply) so anyone here can see it. If you have trouble doing that, you can send it directly to me at rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbols they spell out).

Edit note before going to sleep: I just want to make sure you realize the code I posted only executes when the workbook is closed (the wording in your last message made me wonder on that).