Combobox & listbox help

[twinpowr]

Hi guys i am trying to get my listbox to filter based on the selection of my combobox, there are 4 values, manager 1,2,3,4
column b of my data list contains the names of the managers, i need the listbox to display only the data based on this selection. The listbox works fine until i try to add this filter.

Using the .additem i can get it to display the dates, and part of the managers name, but run out of column width. there is a total of 9 columns so the line in bold would ultimatley run the entire row from a thru to I. I have tried the rowsource but can not get the to work with the ws.range as my range.

Private Sub CommandButton3_Click()
Dim ws As Worksheet
Dim ilastrow As Long
Dim irow As Long

ListBox1.Clear
Set ws = Worksheets("Data")
ilastrow = ws.Range("A65536").End(xlUp).Row
 
For irow = 1 To ilastrow    'u cannot start from 0 bcoz there is no such cell-A0
    If Trim(ws.Range("b" & irow).Value) = ComboBox1.Value Then
            With ListBox1
           .AddItem Trim(ws.Range("A" & irow).Value) & vbTab & (ws.Range("b" & irow))
    End With
    End If
Next irow

End Sub

I can get it to show all the items if i add them to run one under the other,but they need to run next to each other.

Any help would be appreiciated.

 

[MickG]

Hi, This code will show 9 columns in you "ListBox" based on Combobox1 value/ column "B" value.

[COLOR=navy]Sub[/COLOR] MG28Feb11
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]With[/COLOR] ListBox1
.Clear
.ColumnCount = 9
.ColumnWidths = "50,50,50,50,50,50,50,50,50"
[COLOR=navy]End[/COLOR] With
With Worksheets("Data") 
[COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
 
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
      [COLOR=navy]If[/COLOR] Trim(Dn.Offset(, 1)) = ComboBox1.value [COLOR=navy]Then[/COLOR]
            [COLOR=navy]With[/COLOR] ListBox1
                .AddItem Trim(Dn)
                     [COLOR=navy]For[/COLOR] Ac = 1 To 8
                        .List(.ListCount - 1, Ac) = Dn.Offset(, Ac)
                    [COLOR=navy]Next[/COLOR] Ac
           [COLOR=navy]End[/COLOR] With
     [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[twinpowr]

Awseme,

Thanks Mick

Could not quite figure that one out.

I really appreciate your help

Thanks


Ally.