Pasting into a cell with data validation

[Dave Punky]

Hi all,

I'm having a real issue in regards to being able to paste into a cell which supposedly has data validation on it.

Basically I have a cell with a dropdown which gives one of 3 options. If you try and type manually into this cell it's fine it throws up the error saying you can't do that. However if I paste into the cell it goes completely unchallenged and just sits there.

I also have the same issue with a cell which has data validation for date specific values, I can just copy text into it and it completely defeats the object of having data validation in the first place.

Is there any way of disabling paste into specific cells, so say A1 has validation on it then you can't paste into that cell, you either have to manually type in or make a selection?

Any help would be appreciated!

 

[shg]

You can do miscellaneous things to force pasting values only, but IMO, the easiest way is to do the validation in VBA, which you can enforce at all times.

 

[Dave Punky]

Any suggestions as to how I would do that shg? I'm guessing some kind of live running macro on ThisWorkbook? I sorta know what I'm doing with VBA but with something like that I'd have no idea where to start...

 

[Dave Punky]

I've just been playing around with this and it appears to be both when you paste using right click and also when you Ctrl & V.

There are only 3 cells I need to disable paste in altogether (for all three of these options). The annoying thing is I can't disable paste altoghether as people will need to copy information from a website into it, and I've searched all over the web with no real joy.

If there is anything anyone can think of I would very much appreciate it, or if there is any way of setting up data validation in VBA for when someone pastes into a specific field possibly?

For reference this is an Excel 2003 workbook, and the cells are A2, C2 and E2.

 

[mariog]

I have this from an older thread someone made on another forum...kudos to j-walk.com

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#0000ff]    'Does the validation range still have validation?[/COLOR]
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
[COLOR=#0000ff]'   Returns True if every cell in Range r uses Data Validation[/COLOR]
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[Dave Punky]

Hi mariog,

I've tried that, and put the validationrange as A2 but it still lets me paste into the cell and again any value I like.

Is there any way of setting it up so if you did for example:

Private Sub Preventcopy()

If Paste = Range("A1") Then   <-- I know this isn't the way to word it.
   FormNoCopy.Show
   Range("A1").Clearcontents
End If

End Sub

 

[mariog]

I need to start reading entire posts...give me a moment.

 

[Dave Punky]

That seemed just as good but doesn't appear to be working either - it's still letting me paste into A1 without a challenge.

I've got that in the ThisWorkbook section, I tested it in Sheet 1 too but doesn't seem to want to validate when you paste in for some reason =(

 

[Jonmo1]

It should go in the Sheet's module, not ThisWorkbook.

Does this do anything (pasted in the sheet's module)?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "I Ran"
End Sub

After pasting it, you should get a message box "I Ran" each time you select a different cell..

mariog,
I thought that was a very clever and perfectly sound solution, why did you delete it

 

[mariog]

Sorry for removing the above post, but the problem with it was that it won't let you paste ANY values, even if they didn't infringe the data validation (I hadn't read that you wanted to be able to do that).

This is harder than I thought...

Oh wait! You DO want to disable pasting altogether! Then just

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

Right Click "Worksheet1", "View Code", and paste it there.

jonmo, I deleted it because I misunderstood OP's intentions

Edit #6: It's not working? Try just setting the range to one cell (A1 as is the case here). It seems to work just fine for me!