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
 

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 ---
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top