Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ComboBox w/Grouping (XL97;SR2)

    I have a range (A1:A15) that contains a group of rows (A10:A12). What I would like to do is exclude rows 10 and 11 from appearing in a combobox of a userform. The sourcerow of the combobox would be the range (A1:A15).

    Is it possible?
    Thanks,
    John

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: ComboBox w/Grouping (XL97;SR2)

    You can use the AddItem method of the Combobox. The following example uses the Worksheet Activate event to populate the combobox.

    Private Sub Worksheet_Activate()
    Dim oCell As Range
    For Each oCell In Union([A1:A9], [A12:A15])
    Me.ComboBox1.AddItem (oCell.Text)
    Next
    End Sub


    Place that code in the code module od the sheet with th ecombo box (Right click on the sheet tab and select View code)

    You can substitute the activate event with something more appropriate if required.

    Andrew C

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: ComboBox w/Grouping (XL97;SR2)

    John,

    A serious omission in the code I posted causes repeated entries in the combobox. Change the code to

    Dim oCell As Range
    <font color=red>Me.ComboBox1.Clear</font color=red>
    For Each oCell In Union([A1:A9], [A12:A15])
    Me.ComboBox1.AddItem (oCell.Text)
    Next

    Andrew C

Posting Permissions

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