VBA Stop Workbook From Closing Unless Data Is Filled Complete

hi can I get advice on some VBA scripting that will stop a sheet from closing under these circumstances:

data is entered into a cell under a column call incidents - 3 other cells should also be filled out to complete
the collection of data for the incident. some staff are not filling the 3 other cells just the main incident
column cell and our data collection is incomplete. can we stop the sheet from closing if data exists in
the incident column cell but not in the other 3 cells and then once data is entered the sheet can close off?

cheers

It would have been better if you told us the worksheet name those cells are on and their addresses; but since you did not do that, I'll give you code and you will have to change my guesses of "Sheet1" and cells F6, H11, K6 and K7 to match your actual setup before running the code (the values you have to change are in the Const statements)...

Code:

---

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim FilledCellCount As Long
  Const SheetContainingtheCells As String = "Sheet2"
  Const FourCellsToCheck As String = "F6,H11,K6:K7"
  FilledCellCount = WorksheetFunction.CountA(Worksheets(SheetContainingtheCells).Range(FourCellsToCheck))
  If FilledCellCount > 0 And FilledCellCount <> 4 Then
    Cancel = True
    MsgBox "Since you filled in one of the cells in the Range " & FourCellsToCheck & _
          " then you must fill in the remaining cells in that range.", vbExclamation
  End If
End Sub

---

To install this code, first press ALT+F11 to go into the VBA editor. Once there, look at the right side and locate the panel labeled "Project - VBA Project"... inside that panel are listed all your sheets and an item labeled "ThisWorkbook"... double click that item and copy/paste the above code into the code window that just opened up. That's it. If one of the cells is filled in, but not all four of them, and the user tries to close the workbook, he/she will be shown a MessageBox and then the attempt to close the workbook will be cancelled.

thanks Rick I will give that a go .. any problems I will post that data you mentioned

ok the name of the entire workbook is incident spreadsheet

incident type is entered into column G (Say G6)

the extra data should then be entered into columns M N and O (M6 N6 and O6 would then match data in G6)

so the script must look at data in G (again for example G6) and see data in M N and O 6 so we can be compliant with our data collection..

hope that helps

thanks

Quote:

---

Originally Posted by MATRIXOFFICE

ok the name of the entire workbook is incident spreadsheet

incident type is entered into column G (Say G6)

the extra data should then be entered into columns M N and O (M6 N6 and O6 would then match data in G6)

so the script must look at data in G (again for example G6) and see data in M N and O 6 so we can be compliant with our data collection..

hope that helps

---

Okay, I misunderstood what you wanted when I read your original message... you have many rows of four data cells to check, not just four individual cells. No problem, but I need two pieces of information from you. First, I need the name of the sheet that contains the data being checked (you gave me the workbook name which I do not need). Second, what row in Column G does your data start on (that's actual data, not headers if any)?

thanks Rick

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

Quote:

---

Originally Posted by MATRIXOFFICE

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.

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

Rick any further advice on this problem?

Bump## anymore t houghts on this Rick?

Try this

Code:

---

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

---

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

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

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)

Quote:

---

Originally Posted by MATRIXOFFICE

Bump## anymore t houghts on this Rick?

---

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

Code:

---

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

---

no probs Rick will try it out

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

Quote:

---

Originally Posted by MATRIXOFFICE

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.

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 :(

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

file size exceed forum limit so I will email thanks Rick

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

Code:

---

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

---

Rick thanks it works a treat!