+ Reply to Thread
Results 1 to 13 of 13

VBA data validation last row, as standard

  1. #1
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    VBA data validation last row, as standard

    Hi,
    I have a data validation from a worksheet. I want the standard value op the list to be the last row of the sheet, it that possible?
    Attached Files Attached Files
    Last edited by mdek; 02-01-2016 at 11:55 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,333

    Re: VBA data validation last row, as standard

    What does this mean ...

    I want the standard value of the list to be the last row of the sheet
    The last row on the sheet ... 1048576? Or the last entry in the Data Validation List?

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    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


  3. #3
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    Re: VBA data validation last row, as standard

    Last entry

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,333

    Re: VBA data validation last row, as standard

    OK ... This works for Data Validation applied to Column A

    It will return the last entry in the DV List to any cell in column A that has DV applied to it

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Dynamic Named Range on Sheet2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sheet2!A1:A7

    HTML Code: 
    List
    a
    b
    c
    d
    e
    f

    regards, TMS

  5. #5
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    Re: VBA data validation last row, as standard

    Thank you, the data validation dropdown on sheet1 has to have standaard the last value but the user also must have the option to select a other value. I only get the last row. What is the meaning of the formula on sheet2? thanks in advance

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,333

    Re: VBA data validation last row, as standard

    It would help move things along if you uploaded a sample workbook.

    The formula is a Dynamic Named Range referring to a Data Validation List.

  7. #7
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    Re: VBA data validation last row, as standard

    Hi

    I have uploaded a quick sample.
    The last row / date from sheet1 i want as standard value is B1 sheet2.
    It tried your code but some how im doing something wrong

    If tried also a checkbox, if clicked then the user can change the dropdown otherwise the standard value with be taken. I cant get it to so in not clicked the dropdown is not selectable
    Thanks
    Last edited by mdek; 02-01-2016 at 05:44 AM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,333

    Re: VBA data validation last row, as standard

    See attached updated example.

    Regards, TMS
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    Re: VBA data validation last row, as standard

    Thanks, now i get it, i left my code in, so that was going wrong..
    I have got a checkbox if the its unchecked then the dropdown is unselectable, the problem is that i cannot read the value of the unselectable dropdown

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,333

    Re: VBA data validation last row, as standard

    I have got a checkbox if the its unchecked then the dropdown is unselectable, the problem is that i cannot read the value of the unselectable dropdown
    I have no idea what that means, sorry.

    However, the question you originally asked has been answered

  11. #11
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    Re: VBA data validation last row, as standard

    YEp Thank you very much!

  12. #12
    Registered User
    Join Date
    01-15-2016
    Location
    englanf
    MS-Off Ver
    2007
    Posts
    42

    Re: VBA data validation last row, as standard

    Hi,

    i just can it about the formula, i want the use the same code for a other dv. sheet4 nd sheet14 are the vaules.
    How do i do it with the formula?

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,333

    Re: VBA data validation last row, as standard

    You create a (Dynamic) Named Range to refer to the list, for example, nrList2, and then use that in the Data Validation ...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The code is designed to determine the Named Range and the range it refers to ... so, in theory, it should work for the second list. If the DV is on another sheet, you would need to replicate the Selection Change event handler

+ 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: 3
    Last Post: 06-04-2015, 02:27 PM
  2. [SOLVED] Data Validation: How to clear/delete the content of the cell and not Data Validation List?
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-07-2015, 09:42 AM
  3. Replies: 4
    Last Post: 07-03-2014, 02:37 AM
  4. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  5. Adding Date Data Validation to cells with List Data Validation
    By biggtyme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-03-2013, 09:47 AM
  6. Post Data at End of Column Data with Criteria Also Standard Dev.
    By chemmiah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2013, 09:15 AM
  7. Using Defined Names with Data Validation Depend and Data Validation Multi Select
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-01-2012, 05:36 PM

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