none
add item to combobox with index RRS feed

  • Question

  • hello,

    I need to add a set of items with index in combobox. I am using the code

    Sheet2.ComboBox1.AddItem "item",index

    the items are as follow...(item, index)

    Computer & Peripherals , 249
    Electronics , 259
    HOME N KITCHEN , 282
    Apparels And Accessories , 738
    MOBILES , 932
    MOBILE & TABLET ACCESSORIES , 936
    WATCHES , 947
    COMPUTERS & ACCESSORIES , 955
    BEAUTY N PERSONAL CARE , 969
    FESTIVE OFFERS , 974
    GIFTS N CHOCOLATES , 975
    HEALTH SPORTS N FITNESS , 982
    CLOTHING N ACCESSORIES , 987
    FOOTWEAR , 997

    when i try to add to combobox it gives me an error "Run-time error 5": Invalid Procedure Call or Argument

    Saturday, August 8, 2015 5:21 AM

Answers

  • When you want to assign a 2nd value to an item, you can use the column feature of the combo box. Means any row in the combo box can have 2 values. But for that you have to use an array to store the items:

    Sub Init()
      Dim Arr() As Variant
      With Me.ComboBox1
        .Clear
        'Number of columns
        .ColumnCount = 2
        'The .Value property returns the value of this column (1..ColumnCount):
        .BoundColumn = 2
        'Setup an array with values
        ReDim Arr(1 To 3, 1 To 2)
        Arr(1, 1) = "a"
        Arr(1, 2) = 123
        Arr(2, 1) = "b"
        Arr(2, 2) = 234
        Arr(3, 1) = "c"
        Arr(3, 2) = 456
        'Fill into the combo box
        .List = Arr
      End With
    End Sub
    
    Private Sub ComboBox1_Change()
      With Me.ComboBox1
        If .ListIndex < 0 Then Exit Sub
        MsgBox .Value
      End With
    End Sub

    Read the help to ColumnWidths if you want to change the display width of each column.

    Andreas.




    Saturday, August 8, 2015 9:23 AM
  • Hi Andreas,

    here is the working code...

    Sub GetSubCategory(SelCat As String, SelCom As String)
    
        If doc <> "" Then
            Sheet2.addsheet.Visible = False
            Array1 = Split(doc, "~~")
            count = Evaluate(UBound(Array1))
            Sheet2.OLEObjects(SelCom).Object.Clear
            Sheet2.OLEObjects(SelCom).Visible = True
            ReDim Ars(1 To count, 1 To 2)
            For inx1 = 0 To count
                finalcats = Split(Array1(inx1), "==")
                If UBound(finalcats) <> "0" And UBound(finalcats) <> "-1" Then
                    Sheet2.OLEObjects(SelCom).Object.AddItem finalcats(1)
                    Ars(Evaluate(inx1 + 1), 1) = finalcats(1)
                    Ars(Evaluate(inx1 + 1), 2) = finalcats(0)
                End If
            Next inx1
            Sheet2.OLEObjects(SelCom).Object.List = Ars
    
        End If
    
    End Sub


    Wednesday, August 12, 2015 12:14 PM

All replies

  • The 1st item in a combo box has the index 0 and when you add an item Index must be in the range of 0..ComboBox.Count-1

    Andreas.

    Sub Test()
      With Me.ComboBox1
        .Clear
        .AddItem "a"
        .AddItem "b"
        .AddItem "behind 'a'", 1
        .AddItem "1st Item", 0
      End With
    End Sub

    Saturday, August 8, 2015 6:12 AM
  • When you want to assign a 2nd value to an item, you can use the column feature of the combo box. Means any row in the combo box can have 2 values. But for that you have to use an array to store the items:

    Sub Init()
      Dim Arr() As Variant
      With Me.ComboBox1
        .Clear
        'Number of columns
        .ColumnCount = 2
        'The .Value property returns the value of this column (1..ColumnCount):
        .BoundColumn = 2
        'Setup an array with values
        ReDim Arr(1 To 3, 1 To 2)
        Arr(1, 1) = "a"
        Arr(1, 2) = 123
        Arr(2, 1) = "b"
        Arr(2, 2) = 234
        Arr(3, 1) = "c"
        Arr(3, 2) = 456
        'Fill into the combo box
        .List = Arr
      End With
    End Sub
    
    Private Sub ComboBox1_Change()
      With Me.ComboBox1
        If .ListIndex < 0 Then Exit Sub
        MsgBox .Value
      End With
    End Sub

    Read the help to ColumnWidths if you want to change the display width of each column.

    Andreas.




    Saturday, August 8, 2015 9:23 AM
  • Hi Andreas,

    thanks for your reply.

    using your code i added the items successfully, but i don't know how to retrieve that 2nd value on change or click event of combobox.

    i tried .value, it returns the selected text.

    Ravi,

    Saturday, August 8, 2015 12:48 PM
  • but i don't know how to retrieve that 2nd value on change or click event of combobox.

    Set the BoundColumn property to 2, as I've shown in my code above.

    Andreas.

    Saturday, August 8, 2015 5:08 PM
  • Hi Andreas,

    I set the property BoundColumn to 2 and it worked!

    you saved me a lot of time. Thanks a lot.

    Ravi,

    Tuesday, August 11, 2015 6:00 AM
  • I'm pleased to hear that it works.

    Would you please so kind and mark the post with the code as answer? So it would be easier to find the right answer for followers. Thank you.

    Andreas.
    Tuesday, August 11, 2015 7:01 AM
  • Hi Andreas,

    here is the working code...

    Sub GetSubCategory(SelCat As String, SelCom As String)
    
        If doc <> "" Then
            Sheet2.addsheet.Visible = False
            Array1 = Split(doc, "~~")
            count = Evaluate(UBound(Array1))
            Sheet2.OLEObjects(SelCom).Object.Clear
            Sheet2.OLEObjects(SelCom).Visible = True
            ReDim Ars(1 To count, 1 To 2)
            For inx1 = 0 To count
                finalcats = Split(Array1(inx1), "==")
                If UBound(finalcats) <> "0" And UBound(finalcats) <> "-1" Then
                    Sheet2.OLEObjects(SelCom).Object.AddItem finalcats(1)
                    Ars(Evaluate(inx1 + 1), 1) = finalcats(1)
                    Ars(Evaluate(inx1 + 1), 2) = finalcats(0)
                End If
            Next inx1
            Sheet2.OLEObjects(SelCom).Object.List = Ars
    
        End If
    
    End Sub


    Wednesday, August 12, 2015 12:14 PM