Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Populate Combo Boxes (Excel 97)

    I am interested to know if there is a way to add items to a combo box on a user form without actually going into ALT F11 to put them there. Users from different locations will have a need for different values and I dont want them to have to go into the "guts" of the code to update combo items. Can this be done from a table on a worksheet.

    Private Sub UserForm_Initialize()
    txtFName.Value = ""
    txtPosition.Value = ""
    With cboLocation
    .AddItem "Pt Lincoln"
    .AddItem "Pt Augusta"
    .AddItem "Pt Pirie"
    .AddItem "Whyalla"
    .AddItem "Ceduna"
    .AddItem "Coober Pedy"
    .AddItem "Marla"
    .AddItem "NCRO"
    End With
    cboLocation.Value = ""
    With cboSector
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    .AddItem "4"
    .AddItem "5"
    End With
    cboSector.Value = ""
    txtFName.SetFocus
    End Sub


    Kerry

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate Combo Boxes (Excel 97)

    The RowSource property for a combobox specifies a range of cells on a worksheet that will be used to populate the combobox.
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Populate Combo Boxes (Excel 97)

    Legare I have not seen this done before. Can you point me to an example or explain how I would do this please?

    Kerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Populate Combo Boxes (Excel 97)

    With the combo box on the userform selected, look at the properties window/pane. Press F4 to view Properties, if necessary. You can specify a worksheet range in the RowSource property - see screenshot. You can also set or modify the RowSource in code; the property is a string, so you must put quotes around the range address:

    Me.ComboBox1.RowSource = "Sheet2!C3:C14"

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate Combo Boxes (Excel 97)

    Hi Kerry

    To add to the suggestions already made, you can used a named range as your RowSource property. This might be more flexible for various users who have different numbers of possible choices. Each user can maintain their own entries (perhaps on a separate sheet) by selecting the appropriate cells (range) and then naming it using Insert -> Name -> Define.

    The names will have to be specifically those you use in your code - for instance cboLocationRowSource and cboSectorRowSource. You would use these range names in your code as Hans showed.

    Alan

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Populate Combo Boxes (Excel 97)

    I did it!! Thank you so much for your help.

    This will be very useful and as Alan suggests each user can now maintain their own entries in another sheet. I am working toward making the whole thing totally "self sufficient". Nearly there. Stay tuned for the next question coming up tomorrow.


    Kerry

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate Combo Boxes (Excel 97)

    Or you can create a Dynamic Named Range that will automatically adjust to the number of items in the list. <!post=This Post,257079>This Post<!/post> has links to a couple of sites that show how to do that.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populate Combo Boxes (Excel 97)

    Thanks Legare! For whatever reason, probably to account for superfluous "internal" empty cells, I've ended up writing my own code to maintain named ranges used for filling controls. But this is a very neat "formulaic" method.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •