Beforeclose event - help

[newbie1987]

Hi, new here. was wondering if I could get some help on basic coding.

I am trying to create a before close event to stop someone exiting workbook before filling in certain cells. I am able to put code across 1 field but not multiple.

Code below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(10, 5) = "" Then
MsgBox "this field is required", vbInformation, "REQUIRED"
Cancel = True
End If
End Sub

However i need this on (10,7) and (10,8) and (10,14) (10,15) (10,16) with different messages for each.

thanks,

 

[Joe4]

Welcome to the Board!

One simple way is to just repeat the IF...THEN block under the existing one for each one, i.e.
repeat this part:

If Cells(10, 5) = "" Then
MsgBox "this field is required", vbInformation, "REQUIRED"
Cancel = True
End If

changing the address and message for each one.

 

[newbie1987]

thanks for coming back to me, however I am still having an issue. see below:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(10, 5) = "" Then
MsgBox "req1", vbInformation, "REQUIRED"
If Cells(7, 5) = "" Then
MsgBox "Req2", vbInformation, "REQUIRED"
If Cells(8, 5) = "" Then
MsgBox "Req3", vbInformation, "REQUIRED"
If Cells(14, 5) = "" Then
MsgBox "Req4", vbInformation, "REQUIRED"
If Cells(15, 5) = "" Then
MsgBox "Req5", vbInformation, "REQUIRED"
If Cells(16, 5) = "" Then
MsgBox "Req6", vbInformation, "REQUIRED"
Cancel = True
End If
End Sub

 

[newbie1987]

disregard above, i have it figured out now. thanks a lot for your help.

 

[Joe4]

You are welcome.
Glad I was able to help.

 

[newbie1987]

There is still a workaround that a person can SAVEAS , is there a way to stop them saving the form until they have actioned the above.

above only notifies them if they try and exit form via X in corner but they can get round it by saving as.

thanks

 

[Joe4]

There is still a workaround that a person can SAVEAS , is there a way to stop them saving the form until they have actioned the above.

above only notifies them if they try and exit form via X in corner but they can get round it by saving as.

thanks

There is also a "BeforeSave" event that you can add code to.