Pasting into Cell with Validation

[priceyuk]

Hi,

I have a worksheet which contains cells which are set to only accept numbers.

Unfortunately, Excel allows me to paste text into these cells, defeating the validation.

Don't know whether it's related but for various reasons I have coded the spreadsheet to paste data as past special values.

How can I ensure that data being copied complies with cell validation?

thanks

[Dulanic]

Look into this thread, it may address your needs.

http://www.excelforum.com/excel-gene...alidation.html

[priceyuk]

Look into this thread, it may address your needs.

http://www.excelforum.com/excel-gene...alidation.html

thanks for the quick reply but unfortunately this is not what I'm looking for.

If the data is valid (i.e. the user is copying a numeric field to a field with numeric validation) then I want it to paste. The link in that topic seems to prevent pasting if any kind of validation exists.

Basically, if the data is valid (numeric), it should paste. If not, it shouldn't.
If the user is trying to paste a range then ideally it should paste whatever data is valid.

[Dulanic]

VBA Noob posted this attached file in there. It will allow it to paste if the data is coming from a cell with data validation. The reason for this is that if the person pastes a cell and say it has a 2 for the value, but no data validation, it will clear the data validation so then they can go right back to the cell and put whatever they want. It will also let values be pasted, but not a full cell to prevent what I just said.

[priceyuk]

No sorry, this doesn't achieve what I want (unless I'm missing something)

Basically the data in the clipboard is going to be coming from a source which I do not control (most likely another spreadsheet). It is extremely unlikely that the source cell will have validation.

So the user will be copying a cell/range from one spreadsheet which may/may not have validation. As I said above my spreadsheet will paste data using the paste special values so any validation that might exist will not be pasted, only the value.

[Dulanic]

Data Validation is kind of... useless against pasting. You can try maybe doing the data validation instead with VBA? I found this and modified it some. Would need to change the range to what you want. It does not work if multiple cells are being pasted at once however and I honestly am not able to figure out how to change that or if it is possible. Put this in the sheet code:

 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim VRange As Range, cell As Range
    Dim Msg As String
    Dim ValidateCode As Variant
    Set VRange = Range("A1:C10")
    For Each cell In Target
        If Union(cell, VRange).Address = VRange.Address Then
            ValidateCode = EntryIsValid(cell)
            If ValidateCode = True Then
                Exit Sub
            Else
                Application.EnableEvents = False
                Application.Undo
                cell.Activate
                Application.EnableEvents = True
                MsgBox ("Cell " & cell.Address(False, False) & ":" & ValidateCode)
            End If
        End If
    Next cell
End Sub
Function EntryIsValid(cell) As Variant
    If cell = "" Then
        EntryIsValid = True
        Exit Function
    End If

    If Not IsNumeric(cell) Then
        EntryIsValid = "Non-numeric entry."
        Exit Function
    End If

    EntryIsValid = True
End Function