BeforeClose optional message box

[capinlarry]

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

 

[cerfani]

easiest way would be for an input box to pop up and you type "1" to close or "2" to go back. You can make your own window dialog with two buttons if you create a userform

then instead of putting True on cancel, you test the input and decide to cancel or not

 

[capinlarry]

easiest way would be for an input box to pop up and you type "1" to close or "2" to go back. You can make your own window dialog with two buttons if you create a userform

then instead of putting True on cancel, you test the input and decide to cancel or not

I think I may take a shot at creating a window. I forgot to mention that I am nearly brand new to VBA code but have messed around a little bit with other version of BASIC. I will give this a shot and post some code and whether or not I was successful.

Thanks!


-- removed inline image ---