Copy/Paste problems with Worksheet_Change

[K Dales]

I am using the following Worksheet_Change procedure to validate and organize
a data table as it is being edited. Works fine except when values are pasted
into the table. The procedure fires but it ends prematurely - stepping
through the code the error trapping is activated on the line CheckRange.Value
= Val(CheckRange.Text) but if I remove the On Error statement it does not
throw an error message(???). The error when I debug is "Application-defined
or object-defined error." Also, in trying a few things, I find that if I set
CutCopyMode to False anywhere inside this sub it will immediately revert back
to xlCopy - but the copy mode "marquee" around the cells copied has been
switched off.

Any ideas why this is happening???

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DataRange As Range, CheckRange As Range
Dim NonNumeric As Boolean

On Error GoTo Err

Application.EnableEvents = False
Application.ScreenUpdating = False

With Worksheets("DATA SHEET")
.Unprotect
Set DataRange = .Range("B:B")
If NameToRange("SelectedChart") > 2 Then Set DataRange = .Range("B:C")
NonNumeric = False
For Each CheckRange In Intersect(Target.Cells, DataRange)
If (CheckRange.Row > 1) And (CheckRange.Value <> "") And
IsNumeric(CheckRange.Text) Then
CheckRange.Value = Val(CheckRange.Text)
If NameToRange("SelectedChart") > 2 Then
If Val(Range("C" & CheckRange.Row)) <> 0 Then _
.Range("D" & CheckRange.Row).Formula = "=B" & CheckRange.Row
& "/C" & CheckRange.Row _
Else .Range("D" & CheckRange.Row).ClearContents
Else
.Range("D" & CheckRange.Row).ClearContents
End If
Else
NonNumeric = (CheckRange <> "")
CheckRange.ClearContents
If NameToRange("SelectedChart") > 2 Then
If Val(Range("C" & CheckRange.Row)) <> 0 Then _
.Range("D" & CheckRange.Row).Formula = "=B" & CheckRange.Row
& "/C" & CheckRange.Row _
Else .Range("D" & CheckRange.Row).ClearContents
End If
End If
Next CheckRange
.Protect
End With

Application.ScreenUpdating = True
Application.EnableEvents = True

If NonNumeric Then MsgBox "Data range can only contain numeric data;" _
& vbCrLf & "invalid text was deleted", vbInformation, "ERROR:"

Err:
Application.ScreenUpdating = True
Application.EnableEvents = True
Debug.Print Err.Number, Err.Description
End Sub

 

[Tom Ogilvy]

I tried it and it worked for me. You don't have merged cells or something
do you ?

 

[K Dales]

No, no merged cells. Sheet is protected but as you see I unprotect it.
Other than that the sheet is 2 or 4 columns; A is for chart labels, B for
simple data, B&C for numerator/denominator if user needs to calculate a rate,
and D used to calculate the rate (columns C and D are only unlocked if the
user has chosen to work with a rate via the "SelectedChart"). Scroll area is
set so only the 1st 2 or 3 columns can be entered; the rate calculation
column is outside the scroll area.

Trying to make sure data is valid and some of this may be copied/pasted from
other sheets, possibly with numbers stored as text, so I want to convert to
numbers.

I am using Excel 2000 on Win 2000.

I am wondering if some other code in my sheet is firing but can't see how
since I set EnableEvents off. This one has me stumped.