+ Reply to Thread
Results 1 to 15 of 15

How to read the values from the drop-down of cells containing Data validation?

  1. #1
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    How to read the values from the drop-down of cells containing Data validation?

    Hi,

    I want to read the values of the drop-down (it is created using Data Validation --> List )and compare whether the pasted values match with the values in the drop-down.


    For example , I have for Cell A1 drop-down created using the Data validation List , values are {Clerical , Manager and Professional} . I want to check through VBA Code whether the values pasted in the cell A1 match with those 3 values, if not an error message should display. Please help.





    Thanks
    Mounika

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to read the values from the drop-down of cells containing Data validation?

    Why do you want to use VBA code when the whole point of Data Validation is to trap and report data entry errors with the validation Error alert message.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Hi Richard,
    Yes you are right , data validation will trap and report errors. But it will fail when we copy and paste wrong values into those cells. The validation is not working if we are using the paste option. To control this i need to write a VBA code which reports an error if any wrong value is pasted in the cells other than Clerical,Manager,Professional.
    So,i want to compare the pasted value with the drop-down values. I need a code that reads the drop-down values of the data validation cell. Please let me know if you have any doubts.

    Thanks
    Mounika

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to read the values from the drop-down of cells containing Data validation?

    Pl see attached file. It may be helpful.Worksheet events are used.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Hi Srinivasamurthy,

    Actually ,the file you attached solved my problem but the thing is my head asked me to change the logic of using Validationlist Set Val_rng = Range("ValidationList")

    and asked to read the values from the data validation cell drop-down instead of creating name for the list separately , is it possible ?

    Please let me know , it will be very helpful if you reply

    Thanks
    Mounika

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to read the values from the drop-down of cells containing Data validation?

    Quote Originally Posted by timmu View Post
    Hi Srinivasamurthy,

    ... but the thing is my head asked me to change the logic of using Validationlist Set Val_rng = Range("ValidationList")

    and asked to read the values from the data validation cell drop-down instead of creating name for the list separately , is it possible ?

    Please let me know , it will be very helpful if you reply

    Thanks
    Mounika
    This seems inconsistent. Either you copy and paste stuff to cells and have VBA report any errors, or you use Data Validation. You seem to be wanting to use both.

  7. #7
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Actually I want to use Data Validation for reporting the errors when we type wrong values into the Data Validation drop-down cell. Since Data Validation doesn't handle the copy-pasting of wrong values , i want to use VBA code to report such type of error.

    Thanks
    Mounika

  8. #8
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: How to read the values from the drop-down of cells containing Data validation?

    hi timmu, do you mean sort of that? Try to copy values to A1. If incorrect values are passing through, run code "Enable_Events" and try again. The code deletes wrong values in A1
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Hi , Thanks it is working , great. I want the same for an undefined range say A1:A500 , then how to implement for this , can you please provide
    Can we simply modify in below statement Range("a1") to Range("A1:A500")

    Intersect(Target, Range("a1"))

    Please let me know

    Thanks for the great help

    Timmu

  10. #10
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: How to read the values from the drop-down of cells containing Data validation?

    please check attachment
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to read the values from the drop-down of cells containing Data validation?

    Pl see attached file with changed code.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Hi Srinivasamurthy,
    Thanks for the reply, it's working fine . can you explain me in brief how does the validation.Formula1 works in the below statement. Does it require any named ranges ? or it's inbuilt for data validation.

    Set Val_rng = Range(Range("A1:A5").Validation.Formula1)


    Thanks
    Mounika

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: How to read the values from the drop-down of cells containing Data validation?

    IT Does not require any named ranges. It's inbuilt for data validation.
    If problem is soled, pl mark the thread SOLVED.

  14. #14
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Thanks srinivasamurthy for your extended help in this !! grateful to you !!

  15. #15
    Registered User
    Join Date
    05-30-2014
    Posts
    23

    Re: How to read the values from the drop-down of cells containing Data validation?

    Hi Srinivasamurthy,

    When tried to use the code you provided in Prevent pasting ans (2).xlsm in my sheet , i am getting error just when selecting the correct value from drop-down . I used the same code Validation.Formual1 . I attached the sheet where i implemented my code. E10:E55 contain the Data Validation drop-down i.e.., Resource Designation Column. The source for this drop-down is C3:C5 {Clerical,Manager,Professional}. Can you please check and let me know where the fault is ?
    Can you please tell your email address , the file is very large and forum is not supporting to attache the file.

    Thanks for the great help

+ 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. Replies: 0
    Last Post: 06-16-2014, 10:44 AM
  2. [SOLVED] How to display list of values in cells after using data validation drop down?
    By Yadhvi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-03-2013, 12:15 PM
  3. Data Validation Drop Down Boxes (List) - Values Keep Duplicating
    By CJoQ in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2012, 05:07 AM
  4. Replies: 6
    Last Post: 06-07-2012, 02:28 PM
  5. Replies: 4
    Last Post: 06-07-2012, 10:04 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