Excel list box issue

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

Answer 1

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.

Answer 2

Thanks. It works.