Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Populating a combo control (Excel VBA)

    I had a question today about populating a combo control with a variable list. Say that a combo lists names of people, but the list is updated or changed often. I know that a form combo is populated on the form initialise event using the combo's additem property. But this is adding items one at a time and it is static. Is there a way to populate a combo using a reference to an array of names, and if I change the list of names it updates the combo automatically?
    Any ideas?
    TX
    Regards,
    Rudi

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

    Re: Populating a combo control (Excel VBA)

    You can place the list of names on a worksheet (which can be hidden, if you like), and define a dynamic name that refers to the list. Set the RowSource property of the combo box to the defined name.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating a combo control (Excel VBA)

    Just like that!!!! <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    Is it that simple. There must be a catch!
    TX

    PS: Just BTW, could this be done in Access or VB6? Since range names are not available, what would the approach be in these apps? (If there is a quick answer.)
    Regards,
    Rudi

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

    Re: Populating a combo control (Excel VBA)

    In Access, you use a table or query as Row Source for a combo box, and use code like Me.ComboBox1.Requery to update the list if it has been updated while the form is open.
    In VB6, you can probably set the List property of a combo box to an array, but I don't have VB6, so I'm not certain about that.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating a combo control (Excel VBA)

    Thanks Hans. You have answered quite sufficiently!
    Regards,
    Rudi

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

    Re: Populating a combo control (Excel VBA)

    According to the VB6 documentation:

    "List Property
    Returns or sets the items contained in a control's list portion. The list is a string array in which each element is a list item. Available at design time for ListBox and ComboBox controls through the Properties window; read-only at run time for DirListBox, DriveListBox, and FileListBox controls; read/write at run time for ComboBox and ListBox controls.

    Syntax

    object.List(index) [= string]

    The List property syntax has these parts:

    Part Description
    object - Anobject expression that evaluates to an object in the Applies To list.
    index - The number of a specific item in the list.
    string - A string expression specifying the list item. "

    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
  •