How to limit number of Message Boxes on Validation Error?
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
On 26 Apr., 18:11, Richard R wrote:
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)
|