Data Validation - Why does it not work when pasting values?

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
I have a workbook with data validation per a list of contact names. I set the cell properties so the formula's are hidden and protected the sheet. The user can paste into the cell and the validation remains in place for the cell, but it does not validate the pasted values. Any ideas?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: Data Validation - Why does it not work when pasting valu

Unfortunately, that's the way it is. Paste Special preserves the Validation, but doesn't check the entry. Paste removes the Validation.
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
Re: Data Validation - Why does it not work when pasting valu

John P said:
I have a workbook with data validation per a list of contact names. I set the cell properties so the formula's are hidden and protected the sheet. The user can paste into the cell and the validation remains in place for the cell, but it does not validate the pasted values. Any ideas?

Did you try the link Iridium posted in a similar question you asked?

http://216.92.17.166/board2/viewtopic.php?t=67006&highlight=data+validation
 

John P

Board Regular
Joined
Oct 17, 2002
Messages
108
Re: Data Validation - Why does it not work when pasting valu

Yes. The code in the link did not work because it disallowed the pasting of values in the range that had data validation. Our users require the best of both worlds...validation to let them know when they make mistakes and the ability to paste values and have them validated. I tried to talk them into a database to capture this information because then I could have the validation while allowing pasted values, but it was not a winning proposal.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Re: Data Validation - Why does it not work when pasting valu

The only way your users can have the best of both worlds is to emulate Data Validation in the Worksheet_Change event procedure.
 

Swamp Thing

Active Member
Joined
Aug 16, 2002
Messages
313
Re: Data Validation - Why does it not work when pasting valu

Summing up the discussion so far, either you need to make sure that only Paste-Special-Values will be used, OR you can put references to a separate paste area and validate the referring cells.

OK that's one part of the problem solved; but one would ideally like to use Excel to set up the validation rules, and use VBA as sparingly as possible. So...

Let's define a name, "ValRange", that points to the cells that need to be validated. Then...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Debug.Print Target.Address
valid = True

For Each c In Range("ValRange").Cells
valid = valid And c.Validation.Value
Next c

Debug.Print valid

If Not valid Then
MsgBox "At least one cell is not valid"
Application.Undo
Else
End If
Application.CutCopyMode = False
End Sub
 

scrybbler

New Member
Joined
Nov 3, 2015
Messages
3
Re: Data Validation - Why does it not work when pasting valu

The only way your users can have the best of both worlds is to emulate Data Validation in the Worksheet_Change event procedure.

I'd recommend Protecting your worksheet or workbook to solve the problem with pasting wiping out your validation. Just nerf the protection so your users have as many rights as they need (in the Protect Sheet dialog under "allow all users of this worksheet to"). I enabled anonymous users all rights except "Insert hyperlinks" and now when the sheet is protected I can paste all over without affecting any of the validations :)

So... with that tip... what do you MVPs suggest to solve the other problem -- the fact that Excel doesn't validate pasted data? Should I use Swamp Thing's VBA?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,085
Members
416,010
Latest member
NJT

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
Top