VBA Stop Workbook From Closing Unless Data Is Filled Complete

[Excel Fox]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)  Dim FilledCellCount As Long
  Dim lngRows As Long
  Const SheetContainingtheCells As String = "Sheet2"
  Const CellsToCheck As String = "M|:O|"
  For lngRows = 5 To 150
    If Not IsEmpty(Worksheets(SheetContainingtheCells).Range("G" & lngRows)) Then
        FilledCellCount = WorksheetFunction.CountA(Worksheets(SheetContainingtheCells).Range(Replace(CellsToCheck, "|", lngRows)))
        If FilledCellCount < 3 Then
            Cancel = True
            MsgBox "Since you filled the Range 'G" & lngRows & _
                 "' then you must fill in the corresponding cells in the range - " & Replace(CellsToCheck, "|", lngRows), vbExclamation
            Exit Sub
        End If
    End If
  Next lngRows
End Sub

[snb]

The best and most plausible way to do this is to use a userform; it has been designed for this purpose (control the user's input).

[MATRIXOFFICE]

it is glitching sorry

If Not IsEmpty(Worksheets(SheetContainingtheCells).Range( "G" & lngRows)) Then

says that is a subscript out of range and highlights the above line

[Excel Fox]

You should use double quotes when mentioning the sheet name

So, use "" to wrap SheetContainingTheCells (you have to use the name of your worksheet here)

[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).