+ Reply to Thread
Results 1 to 6 of 6

When pasting, does value exist?

  1. #1
    Registered User
    Join Date
    12-10-2015
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    22

    When pasting, does value exist?

    I have a shared workbook, where it is possible for people to write article numbers in a column, i've made a datavalidation so that you can't write a number that is already there. However if you paste, it doesnt work. So i found a VBA online, which when pasting would check if data validation had been overwritten. It worked perfectly until today. Not sure if there has been an update to Excel, but it doesn't look like datavalidation is overwritten anymore, therefor the VBA code doesnt work.

    So i 've been googeling without much luck for a VBA that checks whether a pasted value already exists, and if it does it would undo the paste, and tell the one pasting, that the values pasted, there is a value(s) that is already in the range.

    The column that you can paste and type values into is named ValidationRange, and it is a column. Can someone maybe help with a quick code?

    Often the people pasting, will paste several values at once into several cells in the column, so it need to check for each pasted cell value, if it already exists in range.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: When pasting, does value exist?

    @KriZo
    You should read this other thread - your issue is EXACTLY the same issue which arises with conditional formatting (destroyed when user pastes into a range!). The solution arrived at in the thread is a good one and could be adapted to your problem.

    Can you tell me ...
    - is user pasting values in ONE row only OR in ONE column only OR sometimes many rows AND many columns
    - is the data validation in ONE column only? (which column?)
    Last edited by kev_; 03-26-2018 at 10:52 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Registered User
    Join Date
    12-10-2015
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    22

    Re: When pasting, does value exist?

    @Kev

    It looks like the solution in the other thread could be used, but i think it needs a bit of tweaking.

    The data validation is in one column only yes, it is C:C. It could be several rows that people copy at once, but the way I have build the workbook, I can't see them pasting any other way than in a single column. If the code could include which cell values were duplicates it could be super, then the people copying would know which values they need to find and exclude from their copy.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: When pasting, does value exist?

    See if this does what you want
    - test in attached file before applying to your "live" file
    - valid entries are accepted, invalid entries replaced with helpful text in cell
    - also handles AutoFill

    I have igored data validation because you said "it doesn't look like Data Validation is overwritten anymore" - but I think you should test that theory

    If Column C already contains duplicates (historic problem) and one of those cells is edited that cell's value will be replaced by the same helpful text

    ALL code should be placed in the sheet module
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kev_; 03-27-2018 at 08:27 AM.

  5. #5
    Registered User
    Join Date
    12-10-2015
    Location
    Aarhus, Denmark
    MS-Off Ver
    2016
    Posts
    22

    Re: When pasting, does value exist?

    @kev

    It works like a charm. Thanks!

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: When pasting, does value exist?

    good click ThreadTools@TopOfThread to mark SOLVED
    thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pasting new row in table, only if row doesn't already exist
    By shoulddt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-16-2016, 06:45 PM
  2. [SOLVED] Check First IF Sheet Is Exist With The Name Of Cell K7 If exist Then run the code
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2015, 10:08 AM
  3. [SOLVED] how to run macro or msgbox when Worksheet does not exist/exist
    By herukuncahyono in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2013, 10:54 PM
  4. Numbers Exist/Not exist in a range
    By ElmerS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2008, 04:34 PM
  5. Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  6. Replies: 3
    Last Post: 03-11-2006, 02:50 PM
  7. Replies: 0
    Last Post: 03-10-2006, 05:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1