Move msgbox out of loop
 

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

Move msgbox out of loop
 

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

--
Cheers
Nigel



"Phil Floyd" wrote in message
...
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

Move msgbox out of loop
 

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
"Phil Floyd" skrev i melding
...
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

Move msgbox out of loop
 

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

"Phil Floyd" wrote in message
...
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

Move msgbox out of loop
 

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

"Phil Floyd" wrote in message
...
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