VBA Stop Workbook From Closing Unless Data Is Filled Complete

[MATRIXOFFICE]

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

[Rick Rothstein]

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

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.

[MATRIXOFFICE]

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

[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

thanks

[Rick Rothstein]

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

[MATRIXOFFICE]

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

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

[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