Edit * I want to create a Workbook_BeforeClose event that checks for three different criteria. The first checks a range (L5:L63) to see if there is any data in it. The second checks to see if there is any data in cell (L65). The third checks to see if there is any data in cell (L66). If (L5:L63) has ANY cells that are not empty, then (L65) AND (L66) must have data entered in them prior to the workbook closing. If (L5:L63) has ANY cells that are not empty, and EITHER (L65) or (L66) are empty, then I want to call a message box to address which cell needs to have data input. If (L5:L63) has ANY cells that are not empty and BOTH (L65:L66) are empty, I would like to call a message box that tells the user to fill out both (L65 and L66).* If all of the above criteria are met, I want to exit the sub, no messages, able to close the workbook. I have added a few formulas on another worksheet within the workbook that allow me to better check that all of my conditions are met. Attached here: I am leaving the original message attached below:* I have a Workbook_Before Close sub that has three different criteria. First it is supposed to check if all the cells in a range are empty (L5:L), if they are then I want it to exit the sub. If ANY cell in the range is not empty, but two specific cells (L65 and L66) are empty, it should return a message box telling the user that the two cells need to have values in them (or one cell depending on if one or the other are empty). If you try to close the workbook with any cell in range (L5:L) with data in it, it should stop you from closing it until either a) the data in (L5:L) is cleared, or b) data is input into cells L65 AND L66. Right now it is allowing the workbook to be closed when the range is not empty and the two cells are.
Below is the code I have tried.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rngCell As Range
Dim lngLstRow As Long
If ActiveSheet.Name = "Instruction & Data Input" Then
lngLstRow = ActiveSheet.UsedRange.Rows.Count
For Each rngCell In Range("L5:L" & lngLstRow)
If rngCell.Value = "" Then
Exit Sub
Exit For
End If
Next
For Each rngCell In Range("L5:L" & lngLstRow)
If rngCell.Value <> "" And rngCell.Offset(2, 0) = "" Then
MsgBox ("Please add battery terminal connection (+) resistance reading to sheet in yellow highlighted cells. This reading is taken from the incoming charger controller connection to the first battery terminal lug. ")
Exit For
End If
Next
For Each rngCell In Range("L5:L" & lngLstRow)
If rngCell.Value <> "" And rngCell.Offset(3, 0) = "" Then
MsgBox ("Please add battery terminal connection (-) resistance reading to sheet in yellow highlighted cells. This reading is taken from the last battery to the out going charger controller connection.")
Exit For
End If
Next
End If
End Sub
Try this out:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range, rng As Range, lngLstRow As Long, ws As Worksheet, v, stopClose As Boolean, colOffset
Set ws = ThisWorkbook.Worksheets("Instruction & Data Input") 'reference specific worksheet
lngLstRow = ws.Cells(Rows.Count, "L").End(xlUp).Row 'last cell in Col L with content
If lngLstRow < 5 Then Exit Sub 'range is empty?
'check each cell in Col L used range
For Each c In ws.Range("L5:L" & lngLstRow).Cells
v = c.Value
If Len(v) > 0 Then
For Each colOffset In Array(2, 3) 'checking cells 2 and 3 columns to the right
With c.Offset(0, colOffset) 'the cell to be checked
.FormatConditions.Delete 'clear any existing CF flag
If Len(.Value) = 0 Then 'empty?
AddCFFill .Cells(1), vbRed 'flag the cell
stopClose = True 'at least one cell with a problem
End If 'required cell is empty
End With
Next colOffset 'check next column
End If
Next c
If stopClose Then 'any cells with a problem? Alert the user and cancel close
MsgBox "Some required values are missing! (see red-filled cells)" & vbLf & _
"...rest of message here describes what's missing"
Cancel = True 'cancel the close
End If
End Sub
'Add a fill to a cell/range using a FormatCondition
' Allows to revert to any previous fill on deleting the CF
Sub AddCFFill(c As Range, clr As Long)
With c.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
.SetFirstPriority
.Interior.Color = clr
End With
End Sub
Edit - updated:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim c As Range, rng As Range, ws As Worksheet, v, stopClose As Boolean, msg As String
Dim L65empty As Boolean, L66empty As Boolean
Set ws = ThisWorkbook.Worksheets("Instruction & Data Input") 'reference specific worksheet
If Application.CountA(ws.Range("L5:L63")) = 0 Then Exit Sub 'nothing to check
ws.Range("L65:L66").FormatConditions.Delete 'clear any flags
L65empty = Len(ws.Range("L65").Value) = 0
L66empty = Len(ws.Range("L66").Value) = 0
If (Not L65empty) And (Not L66empty) Then Exit Sub 'all ok
'one or both are empty
If L65empty And L66empty Then
msg = "Please fill in L65 and L66"
AddCFFill ws.Range("L65:L66"), vbRed
ElseIf L66empty Then
msg = "Please fill in L66"
AddCFFill ws.Range("L66"), vbRed
Else
msg = "Please fill in L65"
AddCFFill ws.Range("L65"), vbRed
End If
MsgBox "If any values are entered in L5:L63 then other values must be filled!" & _
" (see red-filled cells)" & vbLf & vbLf & _
msg
Cancel = True 'cancel the close
End Sub