Prev: Color Variation
Next: Open Workbook to Specific Cell
From: Richard R on 26 Apr 2010 12:11
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)
From: Per Jessen on 26 Apr 2010 12:56
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 <richardros...(a)googlemail.com> 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)

From: Richard R on 13 May 2010 04:43
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
 | 
Pages: 1
Prev: Color Variation
Next: Open Workbook to Specific Cell