PDA

View Full Version : Userform Listbox Filter using comboBox



nimesh29
09-26-2017, 06:37 AM
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: 20469

Kenneth Hobs
09-26-2017, 08:05 AM
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.

nimesh29
09-26-2017, 08:47 AM
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-

Kenneth Hobs
09-26-2017, 08:57 AM
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.

nimesh29
09-26-2017, 10:10 AM
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

Kenneth Hobs
09-26-2017, 06:37 PM
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.

nimesh29
09-27-2017, 07:05 AM
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: 20492

Kenneth Hobs
09-27-2017, 08:27 AM
I will look at it tonight. Data autofilter should work.

nimesh29
09-29-2017, 06:40 AM
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-

Kenneth Hobs
09-29-2017, 07:42 AM
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.

nimesh29
09-29-2017, 08:41 AM
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

Kenneth Hobs
10-01-2017, 09:26 PM
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.

nimesh29
10-02-2017, 07:27 AM
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.

Kenneth Hobs
10-02-2017, 09:46 AM
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.

nimesh29
10-02-2017, 11:09 AM
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?

Kenneth Hobs
10-02-2017, 11:14 AM
With few changes, should be fine. I would have to look at what Update does. Can you explain it?

nimesh29
10-02-2017, 11:38 AM
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.

Kenneth Hobs
10-02-2017, 05:27 PM
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.

nimesh29
10-03-2017, 06:16 AM
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-

nimesh29
10-03-2017, 08:30 AM
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?

Kenneth Hobs
10-03-2017, 09:44 AM
Set it in code as you did or set at design in the columnwidths property for the listbox1 control.

nimesh29
10-03-2017, 11:56 AM
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?

Kenneth Hobs
10-03-2017, 01:50 PM
In Sub FillSheet5(), replace the line towards the end after the comment.

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

nimesh29
10-03-2017, 01:57 PM
Perfect, Thank you!

Nimesh-

nimesh29
10-12-2017, 03:20 PM
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.



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

Kenneth Hobs
10-12-2017, 03:58 PM
Did you set the Listbox1.ColumnCount proeprty to 16 or more?

nimesh29
10-13-2017, 06:12 AM
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."



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

Kenneth Hobs
10-13-2017, 11:34 AM
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.


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

nimesh29
10-13-2017, 02:07 PM
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.

Kenneth Hobs
10-13-2017, 02:26 PM
What is the problem with this one?