+ Reply to Thread
Results 1 to 5 of 5

Userform to add names to a validation list for a cell.

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Userform to add names to a validation list for a cell.

    Good Day Folks!

    I am attempting to create a form which uses a validation list for cell A2. I have created the list I want to use in range H2:H5. That part is easy. The hard part is that several folks that have limited Excel skills may need to add new names as new people are needed on the list. What I did was create a UserForm asking for the First and Last names. There is a button under cell A2 which launches the UserForm. The user will populate the first and last name and click a button to send it to the list in column H. The problem now becomes, how do I expand the validation list to include the new cell within the macro?

    I have worked on several solutions but none have worked. I have uploaded an example of what I am trying to do and you will note that I have left the code for the button on the UserForm blank. Any ideas on how to write this one? Any help would be appreciated. Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Userform to add names to a validation list for a cell.

    Hi Tazyote,

    This is a problem for Dynamic Named Ranges. Look it up. Find the answer attached where I've defined a DNR in your Named Range Manager. Then you need to use this named range in your Validation lookup list.

    Please Login or Register  to view this content.
    http://www.contextures.com/xlNames01.html
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    09-24-2013
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Userform to add names to a validation list for a cell.

    Try This..
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Userform to add names to a validation list for a cell.

    Hi moonsaga,

    The LastRow in older version of Excel is less than newer versions of Excel.

    Instead of your code of:
    Set LastRow = Sheet2.Range("H65536").End(xlUp)
    I like to use Rows.Count which adjusts to the version of Excel and do it like:

    LastRow = Cells(Rows.Count, "H").End(xlUp).Row + 1

  5. #5
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Userform to add names to a validation list for a cell.

    Works Great in my new workbook! 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. List Sheet Names in Data Validation List without VBA
    By Al Chara in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-30-2013, 01:49 AM
  2. Replies: 11
    Last Post: 10-06-2011, 02:26 PM
  3. Replies: 1
    Last Post: 10-05-2011, 04:57 PM
  4. Userform Validation - Unique Names & Numbers
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-29-2011, 08:23 AM
  5. How to take a list of names and put in a userform ?
    By dk-iceman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2007, 07:30 AM

Tags for this Thread

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