Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent closing userform | Excel Programming | |||
Prevent closing userform | Excel Programming | |||
Prevent DialogBox closing | Excel Programming | |||
Prevent workbook closing... | Excel Programming | |||
API to prevent closing Excel | Excel Programming |