capinlarry
New Member
- Joined
- Jul 20, 2015
- Messages
- 3
I have a workbook setup to not close if certain cell(s) are not filled out to prevent error (code below). However, it has become rather annoying if we need to open a sheet just for reference as we have to fill in all of the cells to before excel will close. Instead of preventing the worksheet to close altogether I have been trying to come up with a way to have the message box give the user a reminder ("Item X is not filled out") with 2 buttons, either to go back ("Go back"/"Return", etc.), or to confirm closing excel ("Close") with option #1 ("Go back: button) not closing excel and option #2 ("Close" button) causing excel to close. Any ideas?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet: Set ws = Sheets("CBI Datasonde Cal")
If ws.Range("D5").Value = "" Then
MsgBox "Please fill out Datasonde Make", vbCritical
ElseIf ws.Range("G5").Value = "" Then
Cancel = True
MsgBox "Please fill out Datasonde Model", vbCritical
ElseIf ws.Range("J5").Value = "" Then
Cancel = True
MsgBox "Please fill out Serial #", vbCritical
ElseIf ws.Range("M5").Value = "" Then
Cancel = True
MsgBox "Please fill out Station", vbCritical
ElseIf Application.WorksheetFunction.CountBlank(ws.Range("D9:D12")) > 0 Then
Cancel = True
MsgBox "Please fill out all blank cells under PRE-DEPLOYMENT CALIBRATION", vbCritical
ElseIf Application.WorksheetFunction.CountBlank(ws.Range("E41:E44")) > 0 Then
Cancel = True
MsgBox "Please fill out all blank cells under PREDEPLOYMENT MAINTENANCE/SETUP", vbCritical
ElseIf ws.Range("H41").Value = "" Then
Cancel = True
MsgBox "Please fill out all blank cells under PREDEPLOYMENT MAINTENANCE/SETUP", vbCritical
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet: Set ws = Sheets("CBI Datasonde Cal")
If ws.Range("D5").Value = "" Then
MsgBox "Please fill out Datasonde Make", vbCritical
ElseIf ws.Range("G5").Value = "" Then
Cancel = True
MsgBox "Please fill out Datasonde Model", vbCritical
ElseIf ws.Range("J5").Value = "" Then
Cancel = True
MsgBox "Please fill out Serial #", vbCritical
ElseIf ws.Range("M5").Value = "" Then
Cancel = True
MsgBox "Please fill out Station", vbCritical
ElseIf Application.WorksheetFunction.CountBlank(ws.Range("D9:D12")) > 0 Then
Cancel = True
MsgBox "Please fill out all blank cells under PRE-DEPLOYMENT CALIBRATION", vbCritical
ElseIf Application.WorksheetFunction.CountBlank(ws.Range("E41:E44")) > 0 Then
Cancel = True
MsgBox "Please fill out all blank cells under PREDEPLOYMENT MAINTENANCE/SETUP", vbCritical
ElseIf ws.Range("H41").Value = "" Then
Cancel = True
MsgBox "Please fill out all blank cells under PREDEPLOYMENT MAINTENANCE/SETUP", vbCritical
End If
End Sub