VBA Stop Workbook From Closing Unless Data Is Filled Complete

[Rick Rothstein]

sheet names are each month so there are 12 sheets containing data in this case JAN FEB MAR etc etc

Data commences in row 5 down to row 150

Okay, I did not understand we were talking multiple sheets as well. One more question then. Given each sheet is devoted to a month, is there a need to check every sheet every time the workbook is closed? In other words, would there be any entries for the MAR (and beyond) sheet(s) yet? Also, would there be any entries for months prior to the current month? For example, tomorrow is February 1st... when this workbook is opened on February 1st, would there be any need to check the JAN sheet when it is closed later that day? Please answer both of those questions.

[MATRIXOFFICE]

no its all point in time data capture. there is no need to check past sheets only current sheets in use. there are no future entries as the data
is based on incidents that occur. we do not know when and what they are of course ..

[MATRIXOFFICE]

Rick any further advice on this problem?

[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?