Triggering Verification Function

[francislee]

When user use copy and paste to enter some values in the cells, it seem
that copy and paste and bypass the verification function in the cell
For example, I added a verification function in a cell and limits use
to input numeric data only. However, user can still paste som
characters in that cell with no problem. How do I handle this case

 

[Vasant Nanavati]

Unfortunately, pasting into a cell wipes out its data validation.

 

[francislee]

I just think is it possible to add write some VBA codes in the chang
event of each cell and trigger the cell verfication functio
explicitly. Am I correct

 

[Bob Phillips]

Yes, you can

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:G2")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
Cells(3, .Column).Value = Cells(1, .Column).Value + _
Cells(2, .Column).Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)