Prevent closing if values do not match
 

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

Prevent closing if values do not match
 

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" wrote:

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

Prevent closing if values do not match
 

Unsuccessful.

"Sam Wilson" wrote:


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" wrote:

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

Prevent closing if values do not match
 

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("D4:D454").Rows.Count
lCols = Range("D4:D454").Columns.Count

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

columnD = Range("D4:D454").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

On Oct 5, 11:51*am, mark_the_yeti
wrote:
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

Prevent closing if values do not match
 

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.

"JP" wrote:

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("D4:D454").Rows.Count
lCols = Range("D4:D454").Columns.Count

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

columnD = Range("D4:D454").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

On Oct 5, 11:51 am, mark_the_yeti
wrote:
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

Prevent closing if values do not match
 

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


"mark_the_yeti" wrote:

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.

"JP" wrote:

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("D4:D454").Rows.Count
lCols = Range("D4:D454").Columns.Count

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

columnD = Range("D4:D454").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

On Oct 5, 11:51 am, mark_the_yeti
wrote:
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