display the results from range of columns when combobox is selected

[Anchal]

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.

[tehneXus]

Try this sample.xlsm

Regards

[Anchal]

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?

[tehneXus]

Hi,

instead of a listbox i used a listview, please make sure to set the reference to "Microsoft Windows Common Controls 6.0 (SP6)" in VBA Project Window -> Tools -> References.

Regards

[Anchal]

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..?

[tehneXus]

try this one:sample.xlsm

Regards

[Anchal]

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..

[Norie]

To fix the latest problem goto Tools>References... and uncheck anything listed as MISSING.

[Anchal]

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?

[Anchal]

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?

[tehneXus]

Hi,
test this one: sample.xlsm
I replaced

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and made some other changes due to bugs I found.

Regards

[Anchal]

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?

[Anchal]

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?

[tehneXus]

Hi,

I am not getting any error in

Please Login or Register  to view this content.

but you can try

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

instead.

About the column headers: the only way to have column headers in a list box is to set a Range in the RowSource property -> you cannot use the existing table. The only solutions would be using a ListView, as I did at the beginning.. or use an additional invisible helpworksheet -> this will, depending on the amount of rows in your table, slow down the userform.

About the zeros: Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Regards

[Anchal]

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?

[Norie]

I've only just looked at the sample files and I was wondering why you are using a listbox with so many columns.

Wouldn't it make more sense to have a 2 column listbox with the countries in one column and the values in another?

If there are repeating values for a country you could add a number to the country name.

For example for Yacht gloss you would have Netherlands1 and Netherlands2.

[tehneXus]

Hi,

could you please upload the current file which is working for you as I lost track due to all the changes.

And please use Code tags for future posting of Code

Regards