How to limit number of Message Boxes on Validation Error?

[Richard R]

Hi

I have the following code which highlights validation errors when a
user pastes into a range. The problem is that when a large amount of
data is pasted (with a suitably large n number of validation errors)
the msgbox has to be clicked n times before the corrections can be
made.

***Start Code***
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Cells.ClearComments
Dim TempCell As Range
Dim rc As Interger

For Each TempCell In ActiveSheet.UsedRange
If Not TempCell.Validation.Value Then
rc = MsgBox("Please ensure the circled data is entered correctly
(including formatting) and paste as values only", 16, "Data Validation
Error")

ActiveSheet.CircleInvalid

End If
Next

End Sub
**End Code***

Is it possible to have the message box appear once only but for all
erroneous cells to be circled?

I also have a problem with users pasting formatted cells into the
range but that's another problem

Thanks in advance.
(I'm a novice VBA user so please excuse my simplisitic approach)

 

[Per Jessen]

Hi

You can use a boolean variable and test if it has been set True:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

Cells.ClearComments
Dim TempCell As Range
Dim rc As Interger
Dim NoShow As Boolean

For Each TempCell In ActiveSheet.UsedRange
If Not TempCell.Validation.Value Then
If NoShow = False Then
rc = MsgBox("Please ensure the circled data is entered
correctly" & vbLf & _
"(including formatting) and paste as values only", 16,
"Data Validation Error ")
NoShow = True
ActiveSheet.CircleInvalid
End If
Next
End Sub

Regards,
Per

 

[Richard R]

Many thanks Jessen

It seems to work with a small amount of tweaking. This is what I've
finished with:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)


Cells.ClearComments
Dim TempCell As Range
Dim rc As Integer
Dim NoShow As Boolean


For Each TempCell In ActiveSheet.UsedRange
If Not TempCell.Validation.Value Then
If NoShow = False Then
rc = MsgBox("Please ensure the circled data is entered correctly "
& vbLf & "(including formatting) and paste as values only", 16, "Data
Validation Error ")
End If
NoShow = True

ActiveSheet.CircleInvalid

End If
Next
End Sub