+ Reply to Thread
Results 1 to 15 of 15

Userform combobox to populate RowSource for new values in combobox

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Userform combobox to populate RowSource for new values in combobox

    I have a Userform ComboBox with 4 different drop down choices. The RowSource for the ComboBox is Sheet1! A1:A4. The user may want to enter a new value in the ComboBox that is not in the drop down list. Is it possible for the new value add to the existing list on Sheet1 so that it is available from then on?

    Regards

    Stratfordoaks

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Userform combobox to populate RowSource for new values in combobox

    Many, many, ways to do this. One way would be to set up a text box on your userform which would allow the user to input a new value. This new value could then be either directly added to the combo box or added to the range A1:A4 (so it would be A1:A5) and that new range would populate the combo box.

  3. #3
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Userform combobox to populate RowSource for new values in combobox

    You may use:
    Please Login or Register  to view this content.
    and adapt for your control name.

  4. #4
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    Thank you stnkynts. Could you help with the code to add it to the range?

  5. #5
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    Hi Izan. I I changed the name of the ComboBox1 to my comboname of 'PlayAgainst' but it debugged to this.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    Hi Izandol. I ran the userform with the code and the new entry did copy to the list but one row after the last entry in the list. It does not show on the dropdown though. Do I need to make the RowSource a dynamic range?

  7. #7
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Userform combobox to populate RowSource for new values in combobox

    No it must be a named range but it may be static - the code will resize the named range and then re-load the list. I prefer to use List instead of Rowsource but this code will work with a rowsource.

  8. #8
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    Thats perfect now Izandol. Just one thing having tested it now. If I go into the RowSource data and delete my 'test' entries the row source remains that expanded range and subsequent new entries go to the last row leaving a gap of empty cells between them. Is there any kind of range that will expand and contract automatically?

  9. #9
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Userform combobox to populate RowSource for new values in combobox

    Yes - you may use a dynamic range defined with a formula such as:
    =Sheet1!A1:INDEX(Sheet1!A:A,counta(Sheet1!A:A))

    Then you must remove this line of the code:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    I have redefined the named range and removed the code:

    Please Login or Register  to view this content.
    But it debugs to :

    Please Login or Register  to view this content.

  11. #11
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Userform combobox to populate RowSource for new values in combobox

    Perhaps you may post a workbook?

  12. #12
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    No problem. Its attached below.
    Attached Files Attached Files

  13. #13
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Userform combobox to populate RowSource for new values in combobox

    This is my fault - I omitted to make the references absolute. Your named range must be:
    =Sheet3!$A$1:INDEX(Sheet3!$A:$A,COUNTA(Sheet3!$A:$A))

  14. #14
    Registered User
    Join Date
    11-24-2012
    Location
    Stratford-upon-Avon
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Userform combobox to populate RowSource for new values in combobox

    Izandol I really appreciate all of your help. That is perfect now. I could not find any threads that covered this question but I think this one will help many like me.

    Stratfordoaks

  15. #15
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Userform combobox to populate RowSource for new values in combobox

    You are welcome. Naturally this is much easier with a database program instead of a spreadsheet.

+ 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. [SOLVED] Excel Userform: Populate other controls (i.e. textbox & combobox) based on combobox select
    By MileHigh_PhD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2013, 04:50 PM
  2. [SOLVED] Add RowSource to Userform ComboBox based on value on Another ComboBox
    By Baziwan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2012, 01:17 PM
  3. Way to populate combobox rowsource based on previous combobox value
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-08-2012, 12:50 AM
  4. [SOLVED] VBA Userform combobox rowsource and folder value help
    By ds16 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-27-2012, 03:03 PM
  5. Replies: 2
    Last Post: 09-14-2009, 08:34 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