display the results from range of columns when combobox is selected

Hello,

I am attaching a spreadsheet, where column A values are selected in a combobox, the corresponding values in the country range gets i.e from Column B to Column S gets populated in listbox.

For example when user selects Yacht gloss in combobox, then its value 8605.668 gets displayed in listbox and its column header Netherlands gets displayed in a textbox

How can I do this in vba? please help.

Hi tehneXus,

when I try to run the macro it says cannot find project or library for these lines in the code...

With Me.lv1
'.LabelEdit = lvwManual
'.View = lvwReport
End With

also for Dim li As ListItem dont know why? I am using microsoft excel 2007 please help , should I need to convert your sample.xlsm to sample.xlsx?

it is already set to Microsoft Windows Common Controls 6.0 (SP6) and the checkbox next to it is ticked...dont know what to do.. it is still showing compiling errors!!can you kindly amend the code to listbox please..?

Hi tehnexus,

I am so sorry to say that now my excel doesnt support this line of code...its says the same thing cannot find project or library for this line

strTmp = Right(strTmp, Len(strTmp) - Len(DELIMIT))
dic.Item(j) = Split(strTmp, DELIMIT)

But the good news is when I try to run the macro it runs it, but when I choose a item in the combobox it causes this error...

I know you have taken pains to modify the code.. but please... God sake for you able to modify this as well??

Thanks a ton in advance..

thank you Norie.. the above sample file is working fine!!!
now another problem when I try to use the sample code in my original file it gives compile error saying user-defined type not defined for this line of code
Dim dic As New Scripting.Dictionary

Any idea why it says like that?

the above problem was solved by replacing the above line with
Set dic = CreateObject("Scripting.Dictionary")

But now gives error type mismatch for line

If xlCell.Value <> vbNullString Then dont know why? any ideas?

Hello tehnexus,

I notice that I am not getting any column headers here as you did for listitem above? in sample 1 file, can you do the same thing in the user form initialize subrountine? that is very important because , it indicates which volume belongs to which country , also it fills the other fields with zeros which I dont want to happen? any help on that please?

Hello tehnexus,

sorry to bother you again. after running the recent excel file, I end up having type mismatch run time error on these lines of code
For Each vKey In dic.Keys
Me.cmb1.AddItem vKey
Next vKey

in UserForm_Initialize() subroutine.. no idea why it gives errors like this , I tried setting up these statements for dictionaries..

dic.CompareMode = vbBinaryCompare
dic.CompareMode = vbTextCompare

but no use... can you let me know why? do I need to set any property in cmb1 item?

Hello ,

Thanks for solving the problem, now I am extending the sample code to suit my needs which should match 4 criteria , not just one and also lists out the same values as above ...
How can I specify 4 criteria here in this part of code?

With xlWs
Set xlRng = .Columns(1).Find(What:=Me.cmb1.Value, LookIn:=xlValues, lookat:=xlWhole)

If Not xlRng Is Nothing Then
fAddr = xlRng.Address
Do
'fill listbox
Set li = Me.lv1.ListItems.Add
li.Text = xlRng.Offset(, 30).Value
For i = 31 To .Cells(2, .Columns.Count).End(xlToLeft).Column
li.ListSubItems.Add , , xlRng.Offset(, i).Value
Next i
Set xlRng = .Columns(1).FindNext(xlRng)
Loop Until xlRng Is Nothing Or xlRng.Address = fAddr
End If
End With

Above ,its looking for column 1 , my second criteria assume if it looks for column 7 , 3rd looks for column 9 how can I amend the above code?