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 all,

With your current looping approach one of the two message boxes is
going to appear for every single one of the 10,000-12=9,988 rows (let
alone when you factor in each of the columns which potentially can make
the amount much larger). This makes me suspect that each time you press
okay, you are then seeing the next messagebox quickly appear which makes
it seem "that the message box doesn't go away". You could check this by
changing your msgbox title eg:

Code:
--------------------
sgBox "Please make additional corrections", vbExclamation +
vbOKCancel, "TEST" & c.address
--------------------


To add to OssieMac's approach of ending the macro (Exit Sub)...
You could build a range of the cells matching the criteria (w/o using
message boxes) & then loop through each of the resulting cells in a
userform (possibly showing a current value, new value, ignore this one
or next, and a Cancel option to completely stop the process. Tushar
shows an example of creating a range using Union which you may be able
to adapt.

Code:
--------------------
http://www.tushar-mehta.com/excel/tips/findall.html
--------------------



hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112296

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