Required fields at BeforeClose

[hoachen]

I have 10 columns and 5 are required fields. Right now I put all required fields if it check empty then pop-a message box. See code below

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim a As string
Set a = Sheets("LUN Allocate").Range("A3")
If a.Value = "" Then
MsgBox "Pls Enter date in Column A3"
a.Select
GoTo cancelMe
End If

cancelMe:
Cancel = True 'cancels the save event

End Sub

AND SO ON...

This will check each field that are required, but if the user do not want to continue to fill the sheet. (filled out only 3 out of 5 required).there is no way for them to quite or exit the spreadsheet if they don't fill in all 5 required field. How can I implement to have a option for them to select in which they can CONTINUE the required filed or EXIT the spreadsheet before they filled in all required fields.

I come across this function but it did not know how to implement the required fields were need to be filled.

MSG1 = MsgBox("Please enter", vbYesNo)

If MSG1 = vbYes Then
MsgBox "Yes, I want to continue"
GoTo cancelMe
Else
MsgBox "No, Exit now"
End If
Exit Sub

cancelMe:
Cancel = True 'cancels the save event

End Sub


Please advice!
Thanks for you your help!

[ragulduy]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("LUN Allocate").Range("A3").Value = "" Then If MsgBox("Cell A3 not filled in, are you sure you want to exit?", vbYesNo) = vbNo Then Cancel = True
End Sub

?

[hoachen]

Thank you for modified the script. It will works, however, this script will loop through all required questions before exit/close. Can it be just asking all the required fields before close? like:

Please fill in A3, B3, C3..., are you sure you want to to exit. If no, stay at the page and yes close the sheet.

Thanks!

[ragulduy]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("LUN Allocate").Range("A3").Value = "" Or _
    Sheets("LUN Allocate").Range("B3").Value = "" Or _
    Sheets("LUN Allocate").Range("C3").Value = "" Then _
    If MsgBox("Cell A3 not filled in, are you sure you want to exit?", vbYesNo) = vbNo _
    Then Cancel = True
End Sub

[hoachen]

it is close!!! the msgbox now only tell cell A3 even though it different field that is empty.