Answered by:
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.
- Country Name
- 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.- Marked as answer by zaveri cc Friday, September 5, 2014 1:49 PM
- Edited by Fei XueMicrosoft employee Tuesday, September 9, 2014 7:12 AM
Friday, September 5, 2014 9:00 AM
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.- Marked as answer by zaveri cc Friday, September 5, 2014 1:49 PM
- Edited by Fei XueMicrosoft employee Tuesday, September 9, 2014 7:12 AM
Friday, September 5, 2014 9:00 AM -
Thanks. It works.Friday, September 5, 2014 1:49 PM