When pasting data into a column with data validation I lose validation check

[Brad]

Hi All,
Im using MS excel 2000 sr1, I have set up some basic validation on a
spreadsheet column to say only allow whole numbers between 1 and 10
(as an example). However it is possible to type the number 11 in
another column (with no validation) in a cell and then cut and paste
this number into the column that has validation applied to it. The
data validation to check for numbers between 1 and 10 does not trap
this error or complain I.e. I believe it is taking the formatting from
the cell I typed 11 in (which has no data validation) and applying it
to the cell Im pasting to, which is in the column that has data
validation (I hope this makes some sense).
I understand you can do paste specials but unfortunately my users
often work on their own spreadsheets and then just paste (Ctrl-C,
Ctrl-V) in large chunks of data so I want an automated method to
enforce the validation if possible!? Drop down lists etc is not really
option, I believe it may need to be some code / macro or some global
option I can activate.

Any ideas greatly appreciated

many thanks
Brad

 

[Frank Kabel]

Hi Brad
not much you can do against this copy/paste problem. The only thing
would be creating an event macro which checks the entries manually. So
there's no such option to disable this copy/paste behaviour.

 

[Guest]

Just an idea, but what if you create a macro with a button on the spreadsheet that paste special/values and tell your people to use that button instead of Ctrl+V when pasting? Would that work?

 

[jeff]

Hi,

Frank's correct. Try something like this
(It's only looking at cells C1 thru C5):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C1:C5")) Is Nothing
Then Exit Sub
With Target
If InStr(1, Str(.Value), ".") Then MsgBox ("Nt an
integer")
Select Case .Value
Case 1 To 10
Case Is < 1
MsgBox ("You entered " & .Value)
Case 11
Range("C2").Select
Case Else
MsgBox ("You entered an invalid number")
End Select
End With
End Sub

Jeff

 

[Dave Peterson]

Another option that's not as pretty.

Insert a helper column adjacent to the cells where you want the data entry.

Put the equivalent of your data|validation in that cell. But make it return a
warning:

=if(dataInC3IsNotValid,"<--Error, Please Correct","")

(use a nice expression for that if statement.)

Then you could check to see if there are errors in any important calculation:

=if(countif(d1:d999,"*error*")>0,"Please correct your errors",yourformulahere)

If you have code that must run, you could also check for errors before it does
anything important.

 

[Brad]

Thanks for the pointers / ideas, much appreciated.

Brad