Hi,
At work we have a spreadsheet with many client's information in it. I have turned on data validation to check for duplicate entries, and it works very well...when someone types in a name. However if they paste it in, it never goes through the validation.
How can I get the entries validated after a paste operation?
Thanks
The copied cell replaces the target cell, completely overwriting it, hence removing the Data Validation.
You'd need VBA to monitor and repair such damage. Might be better to use VBA full stop .
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Ok, and what would that VBA look like? I'm not even sure where to start.
Thanks for your help!
Pasting defeats Data Validation.
There is VBA code at this URL to prevent COPY AND PASTING, DRAG AND DROP http://www.mrexcel.com/forum/excel-q...ste-excel.html
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I don't want to prevent pasting, as pasting is a good thing that reduces typo's and entry errors in this case. I would like something to run on range change, to check for duplicates, and let the user know there is now a duplicate/highlight which one is duplicate kinda thing
Hi, I use something that checks if the same code is already in a line, and copy's the ones that don't exist, if you change it a bit (vba and sheet) it might do what you want. example.xlsm
It checks the booking line on the new shipping tab and inserts it in the other named tab (self explanatory actually) just mess with it a bit and you'll find out how it works. it also removes the pasted data so you have an empty sheet again
Hope it helps
Thinking outside of the box by drawing circles.
You could use Conditional formatting to flag duplicates as they occur. Select the cells with the Data Validation then in Conditional Formatting, New Rule, Format only Unique or Duplicate Values, select Unique and then apply the formatting of your choice
Hi All,
This is what I've got so far
It detects a change in column A, and checks if there is a duplication. If there is, then run Duplicate Identify and Duplicate Notify, else it just ends.
Please Login or Register to view this content.
It seems to work fine, but I have a problem of "Type Mismatch" with line "If Application.WorksheetFunction.CountIf("A:A", 1) Then"
Any help is greatly appreciated!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks