none
Excel list box issue RRS feed

  • Question

  • Hi All,

    I have excel active x controls listbox in sheet1 which is populated with 2 fields from another excel file.

    1. Country Name
    2. Country ID

    Below is the code to populate my list box with country name and ID

    Dim cn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim listbox As Object
    Set listbox = ThisWorkbook.Sheets("SHEET1").listbox1
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
         .ConnectionString = ConnectionString
        .Open
    End With
        
    Set rs = cn.Execute("Select distinct Country,Country_ID from [DataFile$] Where Country IS NOT NULL order by Country")
    If rs.EOF Then Exit Sub
    Dim i As Integer
    i = 0
    With rs
       Do Until .EOF
             
            listbox.AddItem
            listbox.List(i, 0) = ![Country]
            listbox.List(i, 1) = ![Country_ID]
            i = i + 1
            
            .MoveNext
       Loop
    End With
    rs.Close
    cn.Close

    Once country and id is populated i select item(s) from listbox and that item row is copied back to sheet1 using following code

    For i = 0 To listbox1.ListCount - 1
            If listbox1.Selected(i) Then
                findandcopy (listbox1.List(i))
            End If
        Next

    So if i select item China  222 than in above code i= china only. Why is i not equal to china 222?

    When i select one item from list box (China, 222) and when use following code to check what items are selected than msgbox displays only country and not country id

    Sub test()
    Dim listbox1 As Object
    Dim Msg As String
          Dim i As Integer
          Set listbox1 = ThisWorkbook.Sheets("Sheet1").listbox1
          Msg = "You selected" & vbNewLine
          For i = 0 To listbox1.ListCount - 1
              If listbox1.Selected(i) Then
                  Msg = Msg & listbox1.List(i) & vbNewLine
              End If
          Next i
          MsgBox Msg
          
    End Sub

    Thanks,

    Zaveri



    • Edited by zaveri cc Thursday, September 4, 2014 7:58 PM
    Thursday, September 4, 2014 7:54 PM

Answers

  • Hi Zaveri,

    Based on the description, you want to get all colmun value in the listbox control. The listbox control returns the value of bound column. And the default value is 1, so this is resean that the item only return country name. Here is a sample that get the values of first two columns for your reference:

    Sub GetColumnValues()
      For i = 0 To ListBox1.ListCount - 1
              If ListBox1.Selected(i) Then
               msg = msg & ListBox1.Column(0, i) & ListBox1.Column(1, i) & vbNewLine
                
              End If
          Next i
    
    End Sub

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 5, 2014 9:00 AM
    Moderator

All replies

  • Hi Zaveri,

    Based on the description, you want to get all colmun value in the listbox control. The listbox control returns the value of bound column. And the default value is 1, so this is resean that the item only return country name. Here is a sample that get the values of first two columns for your reference:

    Sub GetColumnValues()
      For i = 0 To ListBox1.ListCount - 1
              If ListBox1.Selected(i) Then
               msg = msg & ListBox1.Column(0, i) & ListBox1.Column(1, i) & vbNewLine
                
              End If
          Next i
    
    End Sub

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, September 5, 2014 9:00 AM
    Moderator
  • Thanks. It works.
    Friday, September 5, 2014 1:49 PM