Userform Listbox Filter using comboBox

Hello-
I am trying to Filter certain columns in my ListBox based on a selection from ComboBox in UserForm. I found few sample codes online but, they all seem to refer to a single worksheet and since I have multiple worksheets it made it difficult to follow, wanted to see if there is a way to Filter just the Listbox columns? :confused:
1) I have listbox that is populating from multiple worksheets.
2) Trying to filter out the list using Combobox2 and Combobox3. Selection in combobox2 triggers Rowsource list in Combobox3 and which I like to use to filter Listbox columns by using the Command Button. Combobox3 has multiple Rowsource based on the selection in Combobox2.

Sample file attached: Attachment 20469

You would be better off putting the data into a scratch worksheet. You can then use AutoFilter to filter your data or Advanced filter or such. Obviously, that is all done via a macro.

Kenneth-
:115: I see what you are saying, I already have the data combined on a separate sheet and I could use that sheet to pull the Filtered list.
Do you see any issue with updating the entry and sending it to correct sheet? I am guessing not since I am only using the combined sheet to populate the Listbox.
Currently, I am able to send data to a selected sheet and update those entries on those sheets by selecting the entry on the listbox but, Once the list gets longer it will be difficult to find things or just Pain in the neck! That's where filtered list come in to narrow down the list.

Now just need to put filter command together. Do you know any good examples?

Nimesh-

I may be out of pocket for some time or I would show you. Startoff recording a macro. Sheet1.UsedRange.SpecialCells(xlTypeVisible) or such will probably be used. If I get back and you have no help, I will show you.

Thank you.
In the mean time, I'll give it a go and see if I could get this to work.

-OK...looked into this further and problem might be I am already populating the list box from multiple worksheets and I am asking it to replace current listbox info with Filtered list from combined sheet.
1.) I would need to clear the Listbox1 and replace it with new Filtered list with Command Button.
2.) Then select an item to edit and update
3.) Then clear the filter list and reload full list from all the worksheets with updated information with click of UPDATE command button.

This makes sense but, it might not be possible!?!

-Nimesh

Work up a simple obfuscated example and attach it if you like.

If you are going to get data rows from numerous sheets, pick a row in listbox to edit, and then port the change back to the worksheet it came from, I suggest this. If you have 4 columns of data. Make your scratch sheet have 5. The last column would identify the sheet, and row, it came from.

Since one can determine which row in list box was selected for edit, that row can be edited directly. I guess you would get each column in the row and put the value in a textbox to edit. There should be no need to write back to the sheet it came from and then redo the scratch sheet and listbox contents.

Hi Kenneth-

Thank you for your help with this.
So far I have working:
1- Sending data to different sheets.
2- Populating Listbox from different sheets.
3- Selecting from listbox and updating entry in the correct sheet
4- Combine data to the scratch sheet.

Now how do I go about filtering the data and show that in the listbox. I have 2 combobox that would be used to filter the data to narrow the list for easy editing and not scroll through 100s of entry to find and edit.

Here is sample log file: Attachment 20492

I will look at it tonight. Data autofilter should work.

Hi, Kenneth-

Possible solution:
-Send the data as I am sending currently to different sheets,
-Collect the data in "Combine Data" sheet from all other sheets,
-Then use "Combine Data" sheet to populate My Listbox and from here I am able to an entry in the listbox and edit/ update as required.

Possible Issue:
-I would need to call to combine with both Send and Update button to refresh my Listbox with the latest info. and that could possibly slow the process as the data entry starts increasing?!?

Nimesh-

I like that you used a dynamic named range for your fill lists. Of course one can create the lists from the data if upkeep is too much trouble.

The Columns and Add methods will be slower than more elaborate coded array methods if you have a large dataset to add. Below 10,000 records/rows of data to fill, it should be fine. I guess you can change to the array method if it gets too slow.

For easy coding, I would recommend that the first column heading start at A1 for the DIV sheets. That makes it easier using UsedRange and CurrentRegion.

It will probably be later tonight or tomorrow before I can work on the AutoFilter example. Another time saver might be to not make a scratch sheet and use Autofilter for each DIV sheet.

So, we want to keep the big picture in mind when coding. Howsoever, a solution today though seemingly inefficient but works, is better than fast or/or smaller code some obscure time in the future.

Kenneth- If it's possible to do this without the scratch sheet that would be better!
Regarding the column heading starting at A1, I was planning on having some general information at top of the header. But, I could take that out.

Thanks,
Nimesh

I have not forgotten about you. I have been dealing with some family health issues.

This should give you an idea of what I was talking about. See UserForm1's Initialize event. I have not added the Filter part yet but a similar concept will be used. The 2d sort did not work but there are some more that I wanted to try. Note how column 5 in Listbox1 shows the full address. With this, we know where that row's first column A cell came from. We can set the column width to 0 in the production version to hide.

As I said, while seemingly more work, for a large dataset, it will be worth it in the end.

Hi Kenneth-
No worries and hope all is well with the family.

I just opened the file and going through it and it looks a lot cleaner than the mess of a code I had going on. :o:
I will need to study this to figure out how the additional modules come into play.

This project in its current form is a whole lot better than what I am doing now, entering data into individual sheets.

Thank you.

It would recommend adding a sheet event to remove a row if it is changed to be blank. Until then, I have added a sheet sort as you did to remove the blank row. I also found a bubblesort to sort the final array. If it gets too slow, you can always use a scratch sheet in that workbook or a scratch workbook to add the final array, worksheet sort, refill the array, and then scratch cleanup. That is easily added later.

I should get time to work on the filter part. With this first part done, I don't see the filter part taking much time. Then you can easily do your other parts using Listbox1's column 5.

Cool...
Since this was a sample file I didn't go overboard with the form, I should be able to add additional controls on the userform and columns in the listbox? I'll have close to 16 controls/ columns.
I am getting an error with Update button?

With few changes, should be fine. I would have to look at what Update does. Can you explain it?

Goal with Userform is to create an entry soon as I get something in and fill in much of the information I have and then update additional controls (textboxes) once I have more info. at a later time and Close out the entry. By selecting the entry in the Listbox, all the controls get filled and you are able to update or fill in missing information and send it back with Update button.
Hope this is clear!

I did get an update to work but, couldn't figure out the Search/ Filter.

I decided to use a scratch sheet but still used my array methods. The methods are similar to what I showed you in your other thread.

The Update routine should be reviewed to make sure that you understand it. If you show the DIV sheet for a listbox selection but then let the user change that sheet, you may or may not want that changed. If changed, it may have to be added to that sheet rather than an Update to where the listbox entry shows it.

Kenneth-
I didn't get a chance to review the file yet I'll be doing it soon but, you are Correct on the Update routine, it was done to update the entry to that sheet, not to the Listbox. I run userform initialize to refresh the listbox new updated information.

Nimesh-

Kenneth-
This is exactly what I am looking to do.
Thank you very much for your help with this. :biggrin:

Quick question: How are you controlling the column widths in listbox (Just found it didn't know you could do that...nice)and I could have more than 10 columns with this code?

Set it in code as you did or set at design in the columnwidths property for the listbox1 control.

one more question:
How do you control how many rows get copied to the combined sheet (sheet5)? Currently, it won't copy more 6 rows?

In Sub FillSheet5(), replace the line towards the end after the comment.

Code:

---

'ListBox1.List = a  'Unsorted totally
  Set r = Sheet5.Range("A2").Resize(UBound(a, 1), UBound(a, 2))

---

Perfect, Thank you!

Nimesh-

Hi Kenneth,
Hate to bother you with this again but, I just done setting up my user form and ran into issue after I RUN the filter, when selecting a line in the listbox. When I go to debug it takes me to code below, line [s = ListBox1.List(ListBox1.ListIndex, 16)]. Everything looks okay???
I have 16 controls in the userform.

Code:

---

Private Sub ListBox1_Click()
  Dim i As Integer, A, s As String


  For i = 1 To 16
    Controls("Reg" & i) = ListBox1.Column(i - 1)
  Next i


  s = ListBox1.List(ListBox1.ListIndex, 16)
  s = Split(s, "]")(1)
  s = Split(s, "'")(0)
  ComboBox1.Value = s
 
End Sub

---

Did you set the Listbox1.ColumnCount proeprty to 16 or more?

It's set to '0', I am using code to control column count in the listbox.

Error only occurs when I filter the list and try selecting something, otherwise, I am able to select and controls get filled.
I increased the column count where I needed to, per your sample.

Error: "Could not get the List Property. Invalid argument."

Code:

---

Option Explicit




Private Sub ListBox1_Click()
  Dim i As Integer, A, s As String


  For i = 1 To 16
    Controls("Reg" & i) = ListBox1.Column(i - 1)
  Next i


  s = ListBox1.List(ListBox1.ListIndex, 16)
  s = Split(s, "]")(1)
  s = Split(s, "'")(0)
  ComboBox1.Value = s
 
 
End Sub






'Previous, http://www.vbaexpress.com/forum/showthread.php?60743-Populate-ListBox-from-Multiple-sheets
'http://www.vbbaexpress.com/forum/showthread.php?60840-Userform-Listbox-Filter-using-comboBox&p=370212
Private Sub UserForm_Initialize()
  Dim WS As Worksheet


'To have more then 10 columns in ListBox
 ListBox1.Clear
 ListBox1.List = Range("A3:Q100").Value
 ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
 ListBox1.ColumnWidths = "45;30;45;65;120;45;45;70;45;25;25;45;45;45;65;40" 'COLUMN WITDH OF LISTBOX. Column 17 is not pulled into control
 


  'loop through worksheets to fill combobox1
  ComboBox1.Clear
  For Each WS In Worksheets
    'use the code name in case sheet name changes
    Select Case WS.CodeName
      'exclude these sheets by code name
      Case "Sheet1", "Sheet2", "Sheet5", "Sheet6"
      'Add the rest
      Case Else
        ComboBox1.AddItem WS.Name
    End Select
  Next WS
 
    FillSheet5
   
'<<<<--------------------------Code Below to change Userform Graphic------------------->>>>


'change All Combobox and Textbox colors
    Dim ctrl As Control
    For Each ctrl In UserForm1.Controls
        With ctrl
            Select Case UCase(TypeName(ctrl))
            Case "TEXTBOX", "COMBOBOX"
                .BackColor = RGB(75, 116, 71)
               
            Case "LABEL"
                .BackColor = RGB(134, 172, 65) ' Green -New grass
                .ForeColor = RGB(255, 255, 255)
               
                With .font
                    .Name = "calibri"
                    '.FontStyle = "Bold Italic" 'No, errors
                    .Bold = False
                    .Italic = False
                    .Size = 10
                End With
            Case Else
            End Select
        End With
    Next ctrl
End Sub






'<<<<-------------------------End of Graphics Code------------------------------------->>>>
 
 




Private Sub UserForm_Terminate()
  'ClearAFs
End Sub


Private Sub FillSheet5()
  Dim WS As Worksheet, r As Range
  Dim LastRow As Long, A, b, i As Long
 
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


  With Sheet5
    Intersect(.UsedRange, .Range(.Rows(2), .Rows(.Rows.Count))).Clear
  End With


  'loop through worksheets
  For Each WS In Worksheets
    'use the code name in case sheet name changes
    Select Case WS.CodeName
    'exclude these sheets by code name
    Case "Sheet1", "Sheet2", "Sheet5", "Sheet6"
    'Add the rest
    Case Else
      With WS
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set r = .Range("A2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
        WSsort r 'sort to move blank rows to end
        'Reset range to skip blank rows moved to end
        Set r = .Range("A2:Q" & LastRow).SpecialCells(xlCellTypeVisible)
       
        If Not IsArray(A) Then
          A = r
          'Add full address to column 17 in array
          For i = 1 To UBound(A, 1)
            A(i, 17) = r(i, "A").Address(external:=True)
          Next i
          'Debug.Print LBound(a, 1), LBound(a, 2); UBound(a, 1), UBound(a, 2)
          Else 'Add full address to column 17 in array
            b = r
            For i = 1 To UBound(b, 1)
              b(i, 17) = r(i, "A").Address(external:=True)
            Next i
            'www.cpearson.com/excel/VBAArrays.htm
            A = CombineTwoDArrays(A, b)
        End If
      End With
    End Select
  Next WS
 
  'a = BubbleSort(a, 1) 'sort 2d array, not good
  'Better sort...
 
  'ListBox1.List = a  'Unsorted totally
  Set r = Sheet5.Range("A2").Resize(UBound(A, 1), UBound(A, 2))
  r.Value = A
  WSsort r
  'Set r = Sheet5.Range("A2").Resize(2 + UBound(a, 2), 5)
  ListBox1.List = r.Value
 
 
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
End Sub


'<<<<--------------------------All Command Button Codes------------------->>>>
'Update
Private Sub CommandButton3_Click()
  Dim r As Range, aRow As Long, i As Integer, s As String
  Dim WS As Worksheet
 
  s = ListBox1.List(ListBox1.ListIndex, 16)
  s = Split(s, "]")(1)
  Set WS = Worksheets(Split(s, "'")(0))
  aRow = Range(Split(s, "!")(1)).Row
  Debug.Print aRow


  With WS
    For i = 1 To 16
      .Cells(aRow, i) = Controls("Reg" & i)
    Next i
    .Activate
    .Range("A" & aRow & ":P" & aRow).Select
  End With
 
  UserForm_Initialize
End Sub


'Filter button
Private Sub CommandButton4_Click()
  Dim WS As Worksheet, r As Range
  Dim LastRow As Long, A, b, i As Long
  Dim od As New DataObject, s As String
 
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual


  With Sheet5
    .UsedRange.AutoFilter 8, ComboBox2.Value
    .UsedRange.AutoFilter 11, ComboBox3.Value
    'If .UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then _
      GoTo TheEnd
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set r = .Range("A2:q" & LastRow).SpecialCells(xlCellTypeVisible)
    If r Is Nothing Then GoTo TheEnd
  End With
   
  r.Copy
  od.GetFromClipboard
  Application.CutCopyMode = False
  ListBox1.Clear
  s = od.GetText
  ListBox1.List = StringTo2dArray(s)
 
TheEnd:
  Set od = Nothing
  If Sheet5.AutoFilterMode Then Sheet5.UsedRange.AutoFilter
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
 
'Enter count of the filtered list
  Label27.Caption = ListBox1.ListCount
    UserForm1.Label27.BackColor = RGB(249, 220, 36) 'Yellow
End Sub


Private Sub ClearAFs()
 Dim WS As Worksheet
 For Each WS In Worksheets
    Select Case WS.CodeName
      Case "Sheet1", "Sheet2", "Sheet5", "Sheet6"
      Case Else
        If WS.AutoFilterMode Then WS.UsedRange.AutoFilter
    End Select
  Next WS
End Sub




'To Send to selected sheet
Private Sub CommandButton1_Click()
  Dim cNum As Integer
  Dim x As Integer
  Dim nextrow As Range
  Dim sht As String
  'set the variable for the sheet
  sht = ComboBox1.Value
  'check for values
  If Me.ComboBox1.Value = "" Then
    MsgBox "Select a sheet from the combobox and add the date"
    Exit Sub
  End If
 
  'change the number for the number of controls on the userform
  cNum = 16
  'add the data to the selected worksheet
  Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
  For x = 1 To cNum
    nextrow = Me.Controls("Reg" & x).Value
    Set nextrow = nextrow.Offset(0, 1)
  Next x
  'clear the values in the userform
  For x = 1 To cNum
    Me.Controls("Reg" & x).Value = ""
  Next x
  'Clear Combobox1
  ComboBox1.Clear
  'communicate the results
  MsgBox "The values have been sent to the " & sht & " sheet"
End Sub


'close the userform
Private Sub commandbutton2_Click()


Unload Me
End Sub


'CLEAR BUTTON
Private Sub CommandButton5_Click()
Dim oneControl As Object
 
For Each oneControl In UserForm1.Controls
    Select Case TypeName(oneControl)
    Case "TextBox"
        oneControl.Text = vbNullString
    'Case "CheckBox"
    '    oneControl.Value = False
   
    Case "ComboBox" 'Code below includes comboxes you want to clear
    If InStr(1, "ComboBox1 comboBox2 comboBox3 Reg8 Reg9 Reg10 Reg11 Reg12 Reg13 Reg14 Reg16", oneControl.Name & " ") <> 0 Then
        oneControl.Text = vbNullString
    End If
    End Select
Next oneControl
'Refresh the Listbox
UserForm_Initialize


End Sub


'Clear Search Textbox Button
Private Sub CommandButton6_Click()
ComboBox2.Value = "": ComboBox3.Value = ""


Label27.Caption = ""


'Refresh the Listbox
UserForm_Initialize


End Sub


'To make the Excel sheet Visible
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = False Then
    Application.Visible = False
  End If
  If ToggleButton1.Value = True Then
    Application.Visible = True
   
End If
End Sub
Private Sub CommandButton8_Click()
Dim sat As Long, sut As Byte, s2 As Worksheet, bu As Long


    If ListBox1.ListCount = 0 Then
    MsgBox "No Data to Copy!", vbExclamation
    Exit Sub
    End If
    Set s2 = Sheets("FilterData")
    sat = ListBox1.ListCount
    sut = ListBox1.ColumnCount
    bu = s2.Range("A" & Rows.Count).End(xlUp).Row + 1
     
    s2.Range("A" & bu & ":P" & sat + bu - 1) = ListBox1.List
    MsgBox "Data Copied."


End Sub
'Button to save the file from userfrom
Private Sub CommandButton9_Click()
ThisWorkbook.Save


End Sub


Private Sub CommandButton10_Click() 'Close this Workbook
ThisWorkbook.Close


End Sub


'<<<<--------------------------End of Command button Codes------------------->>>>




'<<<<--------------------------Spin button Code------------------->>>>
'Spin Button for Listbox scrolling
Private Sub SpinButton1_SpinDown()
On Error Resume Next
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub
With Me.ListBox1
        .ListIndex = .ListIndex + 1
    End With
 End Sub


Private Sub SpinButton1_SpinUp()
On Error Resume Next
If ListBox1.ListIndex = 0 Then Exit Sub


With Me.ListBox1
        .ListIndex = .ListIndex - 1
    End With
End Sub
'<<<<--------------------------End Spin Button Code------------------->>>>




'<<<<------------------To Call Code--------------->>>>
Private Sub csipop_Click() 'load CSI Userform
CSIUserform.Show


End Sub

---

I guess you understand that List's 16 is the 17th column item? It looks like your "17th" column value is the External Address. So, your column count must be at least 17.

Code:

---

b(i, 17) = r(i, "A").Address(external:=True)

---

Listbox is showing 17 columns but I am only using columns 16 to fill 16 controls. At the end, I will not show the External address in the listbox.

I was able to get it to work on sample file using the same information so, I am just stumped right now.

What is the problem with this one?