BeforeClose optional message box

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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

cerfani

Well-known Member
Joined
Dec 15, 2014
Messages
1,136
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
 
Last edited:

capinlarry

New Member
Joined
Jul 20, 2015
Messages
3
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 ---
 

Watch MrExcel Video

Forum statistics

Threads
1,099,483
Messages
5,468,874
Members
406,618
Latest member
SAMEERS

This Week's Hot Topics

Top