Listbox with filter data

[bmrsalgas]

Hi can anyone help me with this?

I have a listbox that I already populate using the properties panel, however I need it to filter data using as filter data from a textbox.

can anyone help please.

 

[My Aswer Is This]

Tell us more.
What range do you want to filter and what is the name of the listbox
And filter the values to where

And what column has this filter value?

 

[bmrsalgas]

Hy My Aswer Is This.

So the range is the all C column (the column with user ID that will be put into the textbox)
The range as a name of "jobinfo" same name as the worksheet.
The data starts on A2 and doesn't have a final row because it is suposed to refresh every month, so more info will be added.

Do you need anything else?

Tell us more.
What range do you want to filter and what is the name of the listbox
And filter the values to where

And what column has this filter value?

 

[My Aswer Is This]

Assuming you want to filter Column "C"
And you will filter by the value in a Activex listbox named "ListBox1"

Try this script.

Now your Subject title said ListBox but then in your second post you said TextBox

So modify the script if needed.

Sub Filter_Me()
'Modified 11-26-17 9:45 PM EST
Application.ScreenUpdating = False
Dim ans As String
ans = ActiveSheet.ListBox1.Value
    With ActiveSheet.Range(Cells(1, "C"), Cells(Cells(Rows.Count, "C").End(xlUp).Row, "C"))
        .AutoFilter Field:=1, Criteria1:=ans
    End With
    Application.ScreenUpdating = True
End Sub

 

[bmrsalgas]

Sorry for the Listbox/TextBox confusion.

I will try the code and will tell you something

Assuming you want to filter Column "C"
And you will filter by the value in a Activex listbox named "ListBox1"

Try this script.

Now your Subject title said ListBox but then in your second post you said TextBox

So modify the script if needed.

Sub Filter_Me()
'Modified 11-26-17 9:45 PM EST
Application.ScreenUpdating = False
Dim ans As String
ans = ActiveSheet.ListBox1.Value
    With ActiveSheet.Range(Cells(1, "C"), Cells(Cells(Rows.Count, "C").End(xlUp).Row, "C"))
        .AutoFilter Field:=1, Criteria1:=ans
    End With
    Application.ScreenUpdating = True
End Sub

 

[bmrsalgas]

Hi My Aswer Is This.

So I cound't put your code to work so I am sending some pictures for you to see what is needed

Pic 1 - You can see the Range of cells to be display on the listbox
Pic 2 - You see the Cel N5 on the worksheet named "Users"
Pic 3 - You see the userform named "job_details", where the TextBox1 should be the exact same value of N5 cell and the ListBox1 should filtered using N5 cell value.

https://drive.google.com/open?id=1RJVdDiTfD7czHHGPXtCbV3S2RaCJXCg8

Did I explained myself right?

And thanks for you help.

 

[My Aswer Is This]

I never click on links.
And I never saw anything mentioned in your first post about a UserForm.
If your not able to explain in words what you want then someone else will have to help you.

I'm not even clear what the end result of this project is.
Is it just to filter a listBox?

 

[NoSparks]

@ bmrsalgas
Sharing a sample workbook rather than pictures would increase chances of getting the assistance you seek

 

[My Aswer Is This]

Not from me. Opening workbooks from unknown sources can be dangerous. That is why some companies do not allow Vba to be used.

@ bmrsalgas
Sharing a sample workbook rather than pictures would increase chances of getting the assistance you seek

 

[Kenneth Hobson]

See the Forum Use Guidelines last item for posting tips. https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

I did not see the answer to post #2's column to filter. I used column 3, "C". I only listed columns A:D since I did not see an answer to that either.

Obviously, ControlSource for Textbox1 would be N5.

In the Userform:

Private Sub UserForm_Initialize()
  FilterListBox1
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  FilterListBox1
End Sub

Private Sub FilterListBox1()
  Dim r As Range
  On Error Resume Next
  With Worksheets("JobData")
    Set r = .Range("A1:D" & .Cells(Rows.Count, "A").End(xlUp).Row)
    r.AutoFilter 3, TextBox1
    ListBox1.ColumnCount = r.Columns.Count
    ListBox1.List = cellsToArray(r.SpecialCells(xlCellTypeVisible))
    r.AutoFilter
  End With
End Sub

In a Module:

Function cellsToArray(r As Range)
  Dim a()
  r.Copy
  a = ClipboardToArray
  cellsToArray = a
  Application.CutCopyMode = False
End Function

Function ClipboardToArray()
  Dim cb As Object 'Late Binding for MsForms.DataObject
  'Dim cb As New MsForms.DataObject 'Early Binding
  Dim s() As String, ss() As String, se As String, a()
  Dim i As Long, j As Long
  'Late Binding
  Set cb = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  'On Error GoTo AnError

  'Get data from the clipboard.
  cb.GetFromClipboard
  'Get clipboard contents
  se = cb.GetText(1)
  'Split se, delimiter vbcrlf
  s() = Split(se, vbCrLf)
  ss() = Split(s(0), vbTab)
  ReDim a(LBound(s) To UBound(s), LBound(ss) To UBound(ss))
  '~~> Split each strArray and store in strArray2, delimiter " ".
  For i = LBound(s) To UBound(s)
    ss = Split(s(i), vbTab)
    For j = LBound(ss) To UBound(ss)
      a(i, j) = ss(j)
    Next j
  Next i
  ClipboardToArray = a()

  Exit Function
AnError:
  If Err <> 0 Then Debug.Print Err.Description
End Function