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(SheetContainin gtheCells).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.

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

[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(SheetContainin gtheCells).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).

[MATRIXOFFICE]

file size exceed forum limit so I will email thanks Rick

[Rick Rothstein]

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

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

[MATRIXOFFICE]

Rick thanks it works a treat!