R
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)
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)