Check row range before workbook close

[vivekvasan]

Hello Experts,

I'm trying to tinker with a code that would prevent workbook from closing if the cells in a particular row range are not filled based on first cell criteria. Here is the code so far.. Please share your thoughts/ corrections. Thanks.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ws As Worksheet
Dim Rvalue As Range

Set ws = Sheets("sheet1")

If ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "Complete" Then
If WorksheetFunction.CountA("B3:X7") = 0 Then
Cancel = True
MsgBox "Cell areas for a completed row cannot be left blank"
Else: ActiveWorkbook.Close Savechanges:=True
End If
End If

End Sub

 

[Joe4]

What happens when you test it?
Does it work?
If not, what isn't working right?

Note that you could have an issue if there are multiple sheets in your workbook and you are not on Sheet1 when you try to save it, as this line does NOT reference sheet 1:

If WorksheetFunction.CountA("B3:X7") = 0 Then

So it will just run against whatever the active sheet is when you go to try to save.

You either need to include sheet references in all of your range references, or simply select Sheet1 at the beginning of this code (so that it is the active sheet when the rest of the code runs).

 

[vivekvasan]

It does not show me t

What happens when you test it?
Does it work?
If not, what isn't working right?

Note that you could have an issue if there are multiple sheets in your workbook and you are not on Sheet1 when you try to save it, as this line does NOT reference sheet 1:

If WorksheetFunction.CountA("B3:X7") = 0 Then

So it will just run against whatever the active sheet is when you go to try to save.

You either need to include sheet references in all of your range references, or simply select Sheet1 at the beginning of this code (so that it is the active sheet when the rest of the code runs).

Hi @Joe4,

Thanks for getting back. The issue that I'm seeing is that the workbook can still be closed with empty cells next to the "Complete" category, which is what I want to prevent. The msgbox that I had called in the code doesn't pop up (basically, this code does nothing at this point). I also tried calling in the specific worksheet "With ws" and "End With" for the core portion of this code and it didn't change anything.

 

[Joe4]

What is the name of the module that you have placed this code in?
It MUST be in the "ThisWorkbook" module in order to work automatically.
If you have placed it in one of the Sheet modules, or a module named something like "Module1...", it will not work.

 

[vivekvasan]

What is the name of the module that you have placed this code in?
It MUST be in the "ThisWorkbook" module in order to work automatically.
If you have placed it in one of the Sheet modules, or a module named something like "Module1...", it will not work.

Yep, I did place it in This workbook module. Does not seem to work so far

 

[Joe4]

Did you address the second point I brought up in my first reply?

So it will just run against whatever the active sheet is when you go to try to save.

You either need to include sheet references in all of your range references, or simply select Sheet1 at the beginning of this code (so that it is the active sheet when the rest of the code runs).

 

[vivekvasan]

Test.xlsm
	A	B	C	D
1	Status	Item A	Item B	Item C
2	Not complete
3	Complete
4	Not Complete
5	Complete
Sheet1

 

[Michael M]

I don't understand. If you are checking a single row that has "Complete" in Col "A", why does the count cover rows 3 to 7

If WorksheetFunction.CountA("B3:X7") = 0 Then

 

[vivekvasan]

Hello @Joe4 ,

Attached is the sheet for which I want the code to accomplish the function which I described above. Below is the updated code based on our discussion so far. At this point of time, the below code doesn't show the message box (and let's the file to close) when I leave the cells next to "complete" as empty. Pls advise corrections..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ws As Worksheet
Dim Rvalue As Range

Set ws = Sheets("Sheet1")

With ws

If .Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "Complete" Then
If WorksheetFunction.CountA("B2:D5") = 0 Then
Cancel = True
MsgBox "Cell areas for a completed row cannot be left blank"
Else: ActiveWorkbook.Close Savechanges:=True
End If
End If

End With

End Sub

 

[vivekvasan]

I don't understand. If you are checking a single row that has "Complete" in Col "A", why does the count cover rows 3 to 7

If WorksheetFunction.CountA("B3:X7") = 0 Then

Well said.. I'm pretty much a rookie in macros. So, how do I count upwards to check for empty rows against "Complete" only criteria. I believe if I can get an answer to this, the issue might get resolved. Thanks.