Move msgbox out of loop

  • Thread starter Thread starter Phil Floyd
  • Start date Start date
P

Phil Floyd

Here is the code I am using to check for cells being filled in.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cel In Range("FILLIN01")
If IsEmpty(cel) Then
Ans = MsgBox("Sheet not complete! Would you like to complete
sheet now?", vbYesNo + vbQuestion, "Incomplete Sheet!")
If Ans = vbYes Then
Cancel = True
End If
End If
Next cel
End Sub

There are several cells in range FILLIN01. If 6 of these are not filled
out, I get the message box 6 times. How do I get the message box only once
if any of the cells in the range are blank?

Thanks,
Phil
 
Set another variable to determine the outcome of the loop and use that after
the loop completes... something like.....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim xvalid As Boolean
xvalid = False
For Each cel In Range("FILLIN01")
If IsEmpty(cel) Then xvalid = True
Next cel

If xvalid Then
Ans = MsgBox("Sheet not complete! Would you like to complete
sheet now?", vbYesNo + vbQuestion, "Incomplete Sheet!")
If Ans = vbYes Then
Cancel = True
End If
End If

End Sub
 
Hi Phil

Extending Nigel's approach; I'd add a counter to state "there are 5 empty
cells" and if Yes reply then go to the cells. Something like

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Ans As Long
Dim Cel As Range
Dim i As Long

For Each Cel In Range("FILLIN01")
If IsEmpty(Cel) Then i = i + 1
Next Cel

If i > 0 Then
Ans = MsgBox("There are " & i & _
" empty cells. Complete now ?", _
vbYesNo)

If Ans = vbYes Then
Cancel = True
Range("FILLIN01").Parent.Activate
Range("FILLIN01").Select
End If
End If
End Sub

HTH. Best wishes Harald
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
For Each cel In Range("FILLIN01")
If IsEmpty(cel) Then
Ans = MsgBox("Sheet not complete! Would you like to complete
sheet now?", vbYesNo + vbQuestion, "Incomplete Sheet!")
If Ans = vbYes Then
Cancel = True
Exit Sub
End If
End If
Next cel
End Sub

Exit Sub after the msgbox shows. If the msgbox shows, then you found your
first empty cell. Job done.

Mike F
 
You guys are the greatest. I never thought I would have so many options.
Thanks,
Phil
 
Back
Top