Displaying message boxes for multiple emplty cells upon close

I am trying to create messages that will pop up for empty cells and ranges of empty cells upon closing the document. Here is what I have in VB (please keep in mind I am completely new to VB):


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("CBI Datasonde Cal").Range("D5").Value = "" Then
Cancel = True
MsgBox "Please fill out Datasonde Make", vbCritical
Else
ActiveWorkbook.Close SaveChanges:=True
End If

If Sheets("CBI Datasonde Cal").Range("G5").Value = "" Then
Cancel = True
MsgBox "Please fill out Datasonde Model", vbCritical
Else
ActiveWorkbook.Close SaveChanges:=True
End If

If Sheets("CBI Datasonde Cal").Range("J5").Value = "" Then
Cancel = True
MsgBox "Please fill out Serial #", vbCritical
Else
ActiveWorkbook.Close SaveChanges:=True
End If

If Sheets("CBI Datasonde Cal").Range("M5").Value = "" Then
Cancel = True
MsgBox "Please fill out Station", vbCritical
Else
ActiveWorkbook.Close SaveChanges:=True
End If

If Sheets("CBI Datasonde Cal").Range("D9, D10, D11, D12").Value = "" Then
Cancel = True
MsgBox "Please fill out all blank cells under PRE-DEPLOYMENT CALIBRATION", vbCritical
Else
ActiveWorkbook.Close SaveChanges:=True
End If

If Sheets("CBI Datasonde Cal").Range("E41, E42, E43, E44, H41").Value = "" Then
Cancel = True
MsgBox "Please fill out all blank cells under PREDEPLOYMENT MAINTENANCE/SETUP", vbCritical
Else
ActiveWorkbook.Close SaveChanges:=True
End If
End Sub


The symptoms with this are:

Only displays message box for range upon close if the 1st cell in range is filled out (i.e. if cell D9 is filled out but cells D10 through D12 are not then it will close without displaying the message box for that IF block)
Seems to cycle through message boxes equivalent to the number of other IF sections that are left to be filled out: (i.e. if all IF block cells are filled out except the last IF block it will display the "Please fill out all blank cells under PREDEPLOYMENT MAINTENANCE/SETUP" message 6 times. If all but the last 2 IF sections are filled out it will display the last 2 messages 5 times
If I use colons instead of listing each cell separated by commas (i.e. D9:D12 instead of D9, D10, D11, D12) I get a "Run-time error '13': Type mismatch error



Additionally I have a workbook_open before the IF sections that is working well but just in case it is causing trouble on subsequent sections:

Private Sub Workbook_Open()
Dim fNameAndPath As Variant
fNameAndPath = Application.GetSaveAsFilename(InitialFileName:="", FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Save As")
If fNameAndPath = False Then Exit Sub
Me.SaveAs Filename:=fNameAndPath
End Sub


I am using Excel 2010. Any help is much appreciated!
-Larry

For future reference please use code tags around code that you submit.

Please Login or Register  to view this content.

Worked like a champ, thanks! Sorry, I will be sure to use the code tags next time, it is my first post on this forum. Will be reading up more on VB in excel