Prevent closing if values do not match

[mark_the_yeti]

Hello,

My goal is to prevent the workbook from closing if there is not a value in
Row 4, Column 4 when Row 4, Column 5 does have a value. This condition would
apply for rows 4 to 454.

I think I'm headed in the right direction, but I must be missing something...

So far I have:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

For i = 4 To 454

Set hours = Worksheets("Test").Cells(i, 5)
Set task = Worksheets("Test").Cells(i, 4)

If hours = 0 Then
Cancel = False
Else
If task = 0 Then
Cancel = True
End If
End If

Next i

End Sub

 

[Sam Wilson]

For i = 4 To 454

If not isempty(Worksheets("Test").Cells(i, 5)) Then
if isempty(Worksheets("Test").Cells(i, 4)) then
Cancel = True
exit for
End If
end if

Next i

End Sub

 

[mark_the_yeti]

Unsuccessful.

For i = 4 To 454

If not isempty(Worksheets("Test").Cells(i, 5)) Then
if isempty(Worksheets("Test").Cells(i, 4)) then
Cancel = True
exit for
End If
end if

Next i

End Sub

 

[JP]

How about

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim columnD() As Variant
Dim columnE() As Variant
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

lRows = Range("D4454").Rows.Count
lCols = Range("D4454").Columns.Count

ReDim columnD(1 To lRows, 1 To lCols)
ReDim columnE(1 To lRows, 1 To lCols)

columnD = Range("D4454").Value
columnE = Range("E4:E454").Value

For j = 1 To lCols
For i = 1 To lRows
If (Not IsEmpty(columnE(i, j))) And (IsEmpty(columnD(i, j)))
Then
Debug.Print "condition met in row " & i + 3
Cancel = True
MsgBox "There's a value in column E, but no corresponding
value in column D. Cannot close workbook."
End If
Next i
Next j

End Sub

--JP

 

[mark_the_yeti]

So.... I had this code in "module 1", not "This Workbook"...
When I made the change the code worked fine...
Thank you Sam, JP, for your help.

 

[Sam Wilson]

You may have to consider doing something with a save event too - there's no
reason why the user can't save and then end up crashing/closing through task
manager....