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

 

[Michael M]

UNTESTED

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, lr As Long, r As Long
Set ws = Sheets("Sheet1")
With ws
lr = .Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 3 Step -1
If .Range("A" & r).Value = "Complete" And WorksheetFunction.CountA("B" & r & ":D" & r) <> 3 Then
MsgBox "Cell areas for a completed row cannot be left blank"
Exit Sub
Else: ActiveWorkbook.Close Savechanges:=True
End If
Next r
End With

End Sub

 

[vivekvasan]

UNTESTED

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, lr As Long, r As Long
Set ws = Sheets("Sheet1")
With ws
lr = .Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 3 Step -1
If .Range("A" & r).Value = "Complete" And WorksheetFunction.CountA("B" & r & ":D" & r) <> 3 Then
MsgBox "Cell areas for a completed row cannot be left blank"
Exit Sub
Else: ActiveWorkbook.Close Savechanges:=True
End If
Next r
End With

End Sub

Hello @Michael M, Many thanks for the inputs. This code almost works for my purpose. Just one glitch that I'm facing while testing it out is that, at times, this does not accept leaving blank cells under both "Complete" and "Not Complete" items. However, for this purpose the user can be allowed to leave empty cells under "Not Complete".

Couple of questions about this looping sequence below:

For r = lr To 3 Step -1

1) Does this mean that the code will loop only for the first 3 rows?
2) Does "-1" indicate that the header row also gets included while looping (FYI, I need to exclude title from the loop).

Pls advise. Thanks..

 

[Michael M]

To answer your questions
1) Does this mean that the code will loop only for the first 3 rows?

No, it does from the last row(lr) backwards to the 3rd row

2) Does "-1" indicate that the header row also gets included while looping (FYI, I need to exclude title from the loop).

No, it means it is stepping backwards to row 3, 1 row at a time[/CODE]