Filtering data with userform

[ioncila]

Hi
I'm trying a new approach for a work that I want to optimize to be used by a few members of my team.

By searching hard, I found a very useful form that might be the answer for me and tried do adapt it to my needs.

However, I really need help to work it out, once my vba knowledge is quiet short (very short) to suit code to my file.

I attached a file that is a short similar to what I want do to and the changes I wish to make are described in labels in userform.

So, all I ask is some help and/or orientation to get the right ways

Thank you very much.

[ioncila]

Sorry

Got to attached a new file because when opening userform clears some contents.

[Bob Phillips]

Private Sub ComboBox1_Change()
Dim LastRow As Long
Dim i As Long, j As Long

With Worksheets("Base")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Me.ListBox1.Clear
Me.ListBox1.ColumnCount = 9
For i = 3 To LastRow

If .Cells(i, "A").Value = Me.ComboBox1.Value Then

Me.ListBox1.AddItem .Cells(i, "B").Value
For j = 3 To 10

Me.ListBox1.List(Me.ListBox1.ListCount - 1, j - 2) = .Cells(i, j).Text
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 9) = i
Next j
End If
Next i
End With
With Me
.TextBox1.Value = ""
.TextBox2.Value = ""
.TextBox3.Value = ""
.TextBox4.Value = ""
.TextBox5.Value = ""
.TextBox6.Value = ""
.TextBox7.Value = ""
.TextBox8.Value = ""
.TextBox9.Value = ""
.TextBox10.Value = ""
.TextBox11.Value = ""
.TextBox12.Value = ""
.TextBox13.Value = ""
.TextBox14.Value = ""
.TextBox15.Value = ""
.TextBox16.Value = ""
.TextBox17.Value = ""
End With
End Sub

Private Sub ListBox1_Click()
With Me.ListBox1
Me.TextBox1.Value = .List(.ListIndex, 0)
Me.TextBox2.Value = .List(.ListIndex, 1)
Me.TextBox3.Value = .List(.ListIndex, 2)
Me.TextBox4.Value = .List(.ListIndex, 3)
Me.TextBox5.Value = .List(.ListIndex, 4)
Me.TextBox6.Value = .List(.ListIndex, 5)
Me.TextBox7.Value = .List(.ListIndex, 6)
Me.TextBox8.Value = .List(.ListIndex, 7)
Me.TextBox9.Value = .List(.ListIndex, 8)
Me.TextBox10.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 9).Text
Me.TextBox11.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 10).Text
Me.TextBox12.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 11).Text
Me.TextBox13.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 12).Text
Me.TextBox14.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 13).Text
Me.TextBox15.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 14).Text
Me.TextBox16.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 15).Text
Me.TextBox17.Value = Worksheets("Base").Cells(.List(.ListIndex, 9), 16).Text
End With
End Sub

Private Sub UserForm_Activate()
Dim aryKeys As Variant
Dim LastRow As Long
Dim NextKey As Long
Dim i As Long

With Worksheets("Base")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ReDim aryKeys(1 To LastRow)
For i = 3 To LastRow

If .Cells(i, "A").Value <> "" Then

If IsError(Application.Match(.Cells(i, "A").Value, aryKeys, 0)) Then

NextKey = NextKey + 1
aryKeys(NextKey) = .Cells(i, "A").Value
End If
End If
Next i

ReDim Preserve aryKeys(1 To NextKey)
Me.ComboBox1.List = aryKeys
End With
End Sub

[ioncila]

Thank You very much for your code.
It works in parcial however, once there are few aspects to look over:
Why ListBox doesnt show all row data.
Is there a limit to 9 columns in ListBoxes ( I tried to write over until col 16 but is doesnt work)?

[Bob Phillips]

From VBA Help for ColumnCount:

For an unbound data source, there is a 10-column limit (0 to 9).

[ioncila]

From VBA Help for ColumnCount:

For an unbound data source, there is a 10-column limit (0 to 9).

Is there a way to pass over that?

[Bob Phillips]

Well, I suppose that you could drop the filtered data onto a sratch sheet and bind the listbox to that filtered data.

[ioncila]

Hi
I need to code Buttons 4 (EDIT selected item and update databse) and 5 (ADD NEW Item)

For Button 5, I tried to use this code below, but somehow it misses something to work correctly:
Private Sub CommandButton5_Click()

Dim ws As Worksheet
Dim LastRow As Long
Set ws = Worksheets("Base")
LastRow = ws.Cells(65356, 2).End(xlUp).Row + 1
With ws
.Cells(LastRow, 2).Value = TextBox1.Text
.Cells(LastRow, 3).Value = TextBox2.Text
.Cells(LastRow, 4).Value = TextBox3.Text
.Cells(LastRow, 5).Value = TextBox4.Text
.Cells(LastRow, 6).Value = TextBox5.Text
.Cells(LastRow, 7).Value = TextBox6.Text
.Cells(LastRow, 8).Value = TextBox7.Text
.Cells(LastRow, 9).Value = TextBox8.Text
.Cells(LastRow, 10).Value = TextBox9.Value
.Cells(LastRow, 14).Value = TextBox13.Text
.Cells(LastRow, 15).Value = TextBox14.Value
.Cells(LastRow, 17).Value = TextBox16.Text

End With
End Sub


Wht is wrong?

For Edit buttonits a higher difficult level for me....

[Bob Phillips]

Explain what it is missing.

[ioncila]

I Dont Know.
Code should enter new data in base sheet, but nothing happens

[CHatmaker]

Hi ioncila,

When I read that you also wanted to Update and Add items I thought that my blog might be of help. In it, I have discussed how to select and extract data from any database into Excel. I am currently expanding on this by revealing ways to select data using drop downs on forms to narrow the selection. In the weeks to come I will be discussing how to update databases directly from Excel with what you already own (there's nothing to buy).

I provide code and examples in the blog, Beyond Excel: VBA and Databases (http://itknowledgeexchange.techtarget.com/beyond-excel/)

[Bob Phillips]

I Dont Know.
Code should enter new data in base sheet, but nothing happens

That code works just fine for me.