Hello all, I don't have a clue why when I click on the OK or Cancel
button the MsgBox does not go away. The only way I can get out is to
kill Excel with Control/Alt Delete and end Excel. How do I program
the cancel button to end the sub or at least dismiss the MsgBox when
clicking OK or Cancel? Any suggestions? Thank you all for your
assistance, Ron


Sub testfollowup()
Dim c As Range
For Each c In ActiveSheet.Range("K12:AI10000")
If c.Font.ColorIndex = 3 Then
MsgBox "Please make additional corrections", vbExclamation +
vbOKCancel, "TEST"
Else
MsgBox "Data validated, good job!" & vbNewLine & "If the sheet is
to be printed, clicking on the Print Setup button prepares the file
for printing.", vbExclamation + vbOKCancel, "TEST"
End If
Next c
End Sub

Hi Ron,

You need to assign the response to a variable and then test the variable for
the user's response. You can use If/Then/Else or Select Case for the testing.

Sub testfollowup()
Dim c As Range
Dim userResponse As Variant
For Each c In ActiveSheet.Range("K12:AI10000")
If c.Font.ColorIndex = 3 Then
userResponse = MsgBox("Please make additional corrections", _
vbExclamation + vbOKCancel, "TEST")
Select Case userResponse
Case vbCancel
Exit Sub 'Or other required code
Case vbOK
'Required code here
End Select
Else
userResponse = MsgBox("Data validated, good job!" _
& vbNewLine & _
"If the sheet is to be printed, " & _
"clicking on the Print Setup button " & _
"prepares the file for printing.", _
vbExclamation + vbOKCancel, "TEST")
Select Case userResponse
Case vbCancel
Exit Sub 'Or other required code
Case vbOK
'Required code here
End Select
End If
Next c
End Sub


--
Regards,

OssieMac

hi
you're caught in a loop. the solution i posted earlier displayed the msgbox
only when a red font was found. i added code to your other post to correct
multiple instances of red fonts ie exit sub after the msgbox. my bad for not
seeing that.
the second msgbox you added will display for each and every cell in the loop
that is not red font. this is why you can't get rid of it.
suggestion. during development and testing, choose a much smaller range. and
use step mode to trouble shoot.

what is the purpose of the second msgbox???
what are we trying to do????
if you're just adding a "good job" message at the end then move the second
msgbox OUTSIDE the loop.

post back with more info if i am not understanding.

regards
FSt1

"Ron" wrote:

> Hello all, I don't have a clue why when I click on the OK or Cancel
> button the MsgBox does not go away. The only way I can get out is to
> kill Excel with Control/Alt Delete and end Excel. How do I program
> the cancel button to end the sub or at least dismiss the MsgBox when
> clicking OK or Cancel? Any suggestions? Thank you all for your
> assistance, Ron
>
>
> Sub testfollowup()
> Dim c As Range
> For Each c In ActiveSheet.Range("K12:AI10000")
> If c.Font.ColorIndex = 3 Then
> MsgBox "Please make additional corrections", vbExclamation +
> vbOKCancel, "TEST"
> Else
> MsgBox "Data validated, good job!" & vbNewLine & "If the sheet is
> to be printed, clicking on the Print Setup button prepares the file
> for printing.", vbExclamation + vbOKCancel, "TEST"
> End If
> Next c
> End Sub
>

Good pickup Rob. I was guilty of not assessing what the the rest of the code
was doing. Only providing the answer on how to handle the user response.

The following code displays the cell Id with the error and gives the user
the option of continuing the test (and perhaps make a note of the error cell
Id) or abort the test and fix the error and then run the test again. If
processing is continued then bolError is set to true so that the final msgbox
displays that there are still errors.

The final msgbox/s do not require testing the user response.

Sub testfollowup()
Dim c As Range
Dim bolErrors As Boolean
Dim userResponse As Variant
For Each c In ActiveSheet.Range("K12:AI10000")
If c.Font.ColorIndex = 3 Then
userResponse = MsgBox("Cell " & c.Address(0, 0) _
& " not corrected." & vbCrLf & _
"OK to continue or Cancel to abort test.", _
vbExclamation + vbOKCancel, "TEST")
bolErrors = True
If userResponse = vbCancel Then
Exit Sub
End If
End If
Next c

If bolErrors = False Then
MsgBox "Data validated, good job!" _
& vbNewLine & _
"If the sheet is to be printed, " & _
"clicking on the Print Setup button " & _
"prepares the file for printing.", _
vbExclamation, "TEST"
Else
MsgBox "Errors exist." & vbCrLf & _
"Correct and run test again."
End If

End Sub


--
Regards,

OssieMac