Pasting into a cell with data validation

Dave Punky

Board Regular
Joined
Jan 7, 2010
Messages
133
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
I have this from an older thread someone made on another forum...kudos to j-walk.com

Code:
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
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 =(
 
Upvote 0
It should go in the Sheet's module, not ThisWorkbook.

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

Code:
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
 
Upvote 0
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

Code:
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top