data validation and copy/paste.......

[Guest]

Have defined multiple cells with (varying) length validation rules....as
noted in other threads, data validation is not enforced when user populates
with copy/paste....since copy/paste does trgger "worksheet change" event, how
can I invoke the cells "defined" validation routine from within
"Worksheet_Change"?
My preference is to leverage the cells defined validation settings to avoid
writing individualized subroutines/logic per cell, per worksheet.....

I know using "Worksheet.CircleInvalid" does perform validation of the cells
in defined range, because the invalid ones are "circled".....problem is,
circling cell doesn't "enforce" validation restrictions.

Any/all help is greatly appreciated....

MPR

 

[Norman Jones]

Hi MPR,

See Tom Ogilvy's suggsetion:

http://tinyurl.com/ovmhn

 

[Guest]

thanks for the feedback....the idea of disabling ability to "paste" data
is appealing but not practical.....the need does exist to allow users
to paste into cells, we just need to impose the data validation rules
on the pasted data....for that reason I was (am) searching for a way
to invoke the underlying (internal) data validation routine "manually"....
my thinking was, given that "Worksheet.CircleInvalid" can flag the
cells in violation, there must be some internal subroutine or method
that is being (or can be) called.....

 

[Guest]

Unfortunately I didn't receive the feedback I was looking (hoping) for so I
had to compose a solution that still allowed users to "paste" data into cells
while still enforcing (?) the data validation schema's defined for the
targeted cells...now..while this approcah does NOT enfore the validation
schema, it does provide the user with a MSGBOX informing them the have pasted
invalid data into the cell....following is baseline script executed when
worksheet changes....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rc As Integer
Dim celll As Range
Dim objWorksheet As Object
Set objWorksheet = ThisWorkbook.ActiveSheet
objWorksheet.ClearCircles
objWorksheet.CircleInvalid
For Each celll In Target
If Not celll.Validation.value Then
rc = MsgBox("Data Validation errors exist! " &
celll.Validation.ErrorMessage & " Please correct circled entries!",
vbCritical, "Failure")
Exit Sub
End If
Next
End Sub

Additionally, I added the following subroutine which executes when the
respective worksheet is opened (informing user worksheet contains invalid
data)...

Private Sub Worksheet_Activate()
Dim rc As Integer
Dim celll As Range
Dim objWorksheet As Object
Set objWorksheet = ThisWorkbook.ActiveSheet
objWorksheet.ClearCircles
objWorksheet.CircleInvalid
For Each celll In objWorksheet.Range("B11:B35", "G11:N35")
If Not celll.Validation.value Then
rc = MsgBox("Data Validation errors exist! Please correct circled
entries!", vbCritical, "Failure")
Exit Sub ' exit subroutine if invalid data found because
"CircleInvalid" highlights all.
End If
Next
End Sub


Again, not elegent or fool proof, but definitely makes user aware they have
pasted "invalid" data into cells......

MPR....