Can you help with this code please?

[rob5497]

This code attempts to take what the user selects from ComboBox1 and use that as the criteria to search for a matching record(s) in the table named "Emp_Table". I have attempted to make the list of records returned dynamic so that it only refers to the filtered list, rather than all items. I get a Run Time Error 13, Type Mismatch which errors on the .RowSource = MyRange line. I get multiple errors of different types every time I change something and I don't understand why.

With ListBox2 it should then populate a list box on the same form with the titles of the columns (I only need the data in A & B at the moment) and the relevant data in the rows underneath. I'm stuck and going around in circles, so if you can run this through your VB and provide me with a solution I would appreciate it very much! Thank you.


Private Sub ComboBox1_Change()

Dim LastRow
Dim MyRange As Range
Set MyRange = Range("A1", Range("B2").End(xlDown))
Set sh = ThisWorkbook.Sheets("EMPMaster")

ActiveSheet.ListObjects("Emp_Table").Range.AutoFilter Field:=3, Criteria1:=ComboBox1.value


ListBox2.RowSource = ""

With Worksheets("EMPMaster")
LastRow = .Range("A1").CurrentRegion.Rows.Count
For Each cell In .Range("A1:b" & LastRow).SpecialCells(12)

Next

End With

With Me.ListBox2
.ColumnHeads = True
.ColumnCount = 2
.ColumnWidths = "70,120"
.RowSource = MyRange
End With
End Sub

 

[rlv01]

Instead of .RowSource = MyRange try RowSource = MyRange.Address

 

[rob5497]

Thank you for the reply. What I'm still not getting is a list returned in the ListBox1 that matches the criteria entered in the ComboBox1. Any assistance would be welcomed, thank you.

 

[rlv01]

I get a Run Time Error 13, Type Mismatch which errors on the .RowSource = MyRange line.

My reply was to address the above error. Which I will assume by your total silence on that topic, was resolved. Since I have no idea what the data on your worksheet looks like, I do not know why you cannot get a list returned in the ListBox1 that matches the criteria entered in the ComboBox1. My guess would be that range "MyRange" does not contain the data you want in your listbox, so figuring out why, would be a place to start.