+ Reply to Thread
Results 1 to 9 of 9

Macro to protect data Validation rules

  1. #1
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Macro to protect data Validation rules

    Hi,

    can anyone help me to create macro in order to protect Data Validation rules.

    e.g. If user copy and paste cells from others source which is not in the same validation criteria or not contain any validation rules, the existing validation will gone.

    so, is there any macro which will be able to automatically run to prevent the data validation?

    Regards,
    Farid.

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,173

    Re: Macro to protect data Validation rules

    You could put your validation rules in vba script MODULE. (simply hit record and click on a cell) This will build the module.
    Then you would build code to use all your rules for validation.
    Please Login or Register  to view this content.
    Last edited by ranman256; 05-15-2014 at 11:46 AM. Reason: grammar

  3. #3
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Macro to protect data Validation rules

    Hi ranman256,

    How does it works, can you please clarify?

    e.g.
    If the validation cells is on A1, and the validation criteria is on B1:B5, which is 1 to 5. let say we copy & paste no. ''7'' into cells A1, will macro automatically run to protect cells from pasted or it will check the items copied whether is in the list of validation criteria, then will allow it if within the range and will retrieve validation rules?

    really confused

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Macro to protect data Validation rules

    What about restricting paste function on those data validation range?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Macro to protect data Validation rules

    Hi ,

    Assume there is no restriction on data validation range,

    or Do I need to lock the cell by worksheet protection? If locked, then the cell cannot be changed.


    Regards.
    Farid

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Macro to protect data Validation rules

    Assume that your validation range is A1:A10 and the below code will restrict the copy paste process on those cells.

    Please Login or Register  to view this content.
    Refer the attached file
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    03-28-2014
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Macro to protect data Validation rules

    Hi,

    Thanks. The macro has automatically disable paste function on data validation range.


    Regards,
    Farid

  8. #8
    Registered User
    Join Date
    11-13-2012
    Location
    California
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Macro to protect data Validation rules

    Hi,
    Is there a way to paste what is in the validation range?

    Regards,
    Anthony

  9. #9
    Registered User
    Join Date
    08-14-2020
    Location
    London, England
    MS-Off Ver
    2019 / O365
    Posts
    1

    Re: Macro to protect data Validation rules

    I don't understand the demo file I'm afraid - I was able to copy and paste invalid data without any issue 😕


    I have the same challenge / problem btw, i.e. imagine...
    - A table of invoices
    - Data validation exists on a number of columns, e.g. column A = "supplier" and data validation rule limits option to suppliers that exist in separate list
    - SCENARIO 1 ✅: If the user tries to enter (type) a supplier that does not exist, the entry is rejected
    - SCENARIO 2 ❌: However if the user copies and pastes a supplier that does not exist, this is accepted
    - SCENARIO 3 ❌: Equally, if I use VBA to set A2.value = "invalid supplier" this is also accepted

    Is there a way please where I can prevent scenarios 2 and 3 (without running loops in VBA as I have 60 columns of data and it would be excessive to code validation for all of them)

+ 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. Seeing which cells have Data Validation rules
    By marketshare in forum Excel General
    Replies: 5
    Last Post: 03-27-2011, 07:51 PM
  2. validation rules not working when someone copy paste data on validation cell
    By jthakrar in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2010, 03:36 AM
  3. Replies: 2
    Last Post: 01-03-2010, 05:21 PM
  4. Macro's and Validation rules??
    By Mr.1nOnly in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-10-2007, 04:17 PM
  5. Data Validation Rules
    By Louise in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-10-2006, 08:10 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