Listbox with filter data

bmrsalgas

New Member
Joined
Aug 11, 2017
Messages
26
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.:confused::confused:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,578
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 11, 2017
Messages
26
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,578
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Aug 11, 2017
Messages
26

ADVERTISEMENT

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

New Member
Joined
Aug 11, 2017
Messages
26
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,578
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
@ bmrsalgas
Sharing a sample workbook rather than pictures would increase chances of getting the assistance you seek
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,578
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,174
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top