Originally Posted by
Jacob Hilderbrand
You can try something like this. It will stop closing, as well as tell the user what cells need to be filled out, then it will select those cells.
[vba]
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng1 As Range
Dim Rng2 As Range
Dim Prompt As String
Dim Cell As Range
Dim AllowClose As Boolean
AllowClose = True
Set Rng1 = Sheets("Daily Centre Inputs").Range("D6,F6,C8:C18,I6:I18,A22:K22,A29,A36,H36")
Prompt = "Please check your data ensuring all required " & _
"cells are complete." & vbCrLf & "you will not be able " & _
"to close or save the workbook until the form has been filled " & _
"out completely. " & vbCrLf & vbCrLf & _
"The following cells are incomplete:" & vbCrLf & vbCrLf
For Each Cell In Rng1
If Cell.Value = vbNullString Then
Prompt = Prompt & Cell.Address(False, False) & vbCrLf
AllowClose = False
If Rng2 Is Nothing Then
Set Rng2 = Cell
Else
Set Rng2 = Union(Rng2, Cell)
End If
End If
Next
If AllowClose Then
Else
MsgBox Prompt, vbCritical, "Incomplete Data"
Cancel = True
Rng2.Select
End If
End Sub
[/vba]