Help to filter list

[lordbodom]

Hello. About a year ago I watched a vba lesson on youtube to create a inventory management system. I have the system running but wanted to make a few improvements and would appreciate any type of help.

I have attached the file. In the form/system, what I am trying to do is be able to type a word and have the list filter items that matches that word only. So if i type for eg. "apple" only listings I see are items with "apple".

THere are 2 product fields, I am looking to do this under the "sales/purchase transactions" section. Clicking the product dropdown shows all the products. But once this list gets long, it gets a bit harder to find what you are looking for. So trying to see if I can change it so that typing the word will filter out the search.

Thank you in advance.

[anish.ms]

Try this
change the Combobox (cmb_Product) style from frmStyleDropDownList to frmStyleDropDownCombo



Private Sub cmb_Product_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim Typematch As String
Dim i As Long

Typematch = LCase(cmb_Product.Text)

With Me.cmb_Product
For i = 0 To .ListCount - 1
On Error Resume Next
If LCase(.List(i)) Like "*" & Typematch & "*" Then
'do nothing
Else
.RemoveItem (i)
End If
On Error GoTo 0
Next i
End With

If Len(cmb_Product.Text) = 0 Then Call Refresh_Product_List

End Sub


I just thought to give a try. Definitely there will be a better way to do this, which the expert members in this forum may explain

[Paul_Hossler]

I'd set the control's MatchEntry = fmMatchEntryFirstLetter

28780

[lordbodom]

I'd set the control's MatchEntry = fmMatchEntryFirstLetter

28780

Wouldnt this just match the first letter? Im trying to see if I can match words, multiple letters. Thanks.

[Paul_Hossler]

Wouldn't this just match the first letter?

Yes, but

1. It's easy
2. It's fast
3. It gets you close

I think I've seen what you're asking about, but IIRC it involves reading keystrokes, repositioning the display, etc. Whole lot of work

[lordbodom]

Yes, but

1. It's easy
2. It's fast
3. It gets you close

I think I've seen what you're asking about, but IIRC it involves reading keystrokes, repositioning the display, etc. Whole lot of work

Yeah, it works currently with the first letter. Issue is with a large list, there can be many products that start with the same letter. Trying to find a solution for that.

[Paul_Hossler]

Not exactly using the Combobox, but uses a Listbox as a search box

Has the advantage of being easy

There's a number of efficiencies that could be made, especially if the list is sorted, but the complexity didn't seem worth it

[anish.ms]

Thanks for sharing this Paul!

[Paul_Hossler]

Tweaked the xlsm a little

[lordbodom]

Thanks. This is what im looking for, but being limited in code, not sure where to put/change this code :(.

[Paul_Hossler]

28783

Replace the Product combobox with a textbox and a list box

Integrate the event handlers from test_2


Test_2 only has the Product selection and the Save button working

Lot of existing code will need to be modified and the Userform format changed for cosmetics

[lordbodom]

Thanks. This seems a bit to complicated for my skillset. Was trying to see if there really was a simple solution, doesnt look like there is.

[Paul_Hossler]

It really isn't too complicated

Replace your combobox with a textbox to type in and a list box to pick from

In Userform_Initialize, build an array of the Products (or whatever)




Option Explicit


Dim aryProducts As Variant


Private Sub UserForm_Initialize()
Dim r As Range




With Worksheets("Product_Master")
Set r = .Cells(2, 2)
Set r = Range(r, r.End(xlDown))

aryProducts = Application.WorksheetFunction.Transpose(r)
End With

Me.tbSearch.Text = vbNullString


End Sub


In Textbox_Change, fill the Listbox (Listbox AddItem) with Products that start with what is in the Textbox



Private Sub tbSearch_Change()
Dim i As Long
With Me

.lbProduct.Clear

If Len(.tbSearch.Text) = 0 Then Exit Sub


For i = LBound(aryProducts) To UBound(aryProducts)
If UCase(Left(aryProducts(i), Len(.tbSearch.Text))) = UCase(.tbSearch.Text) Then
.lbProduct.AddItem aryProducts(i)
End If
Next i

End With


End Sub





Save/Process/Select the listbox item that is highlighted



Private Sub CommandButton8_Click()
With Me
If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value

.Hide
End With

Unload Me


End Sub

[lordbodom]

Thanks Paul. I think whats really throwing me off isnt just trying to get the search to run the way I want, but also because the search is "linked" to other components in the sheet. Like picking a product initiates adding/removing from the inventory and also adding to the daily sales and profit margins. This is what I feel is throwing me off because adding the search code might mess these other links.

Really new to this so I might be completely off base. I'll try to play around with your tips to see if I can get anywhere.

Thanks a bunch for your assistance!



It really isn't too complicated

Replace your combobox with a textbox to type in and a list box to pick from

In Userform_Initialize, build an array of the Products (or whatever)




Option Explicit


Dim aryProducts As Variant


Private Sub UserForm_Initialize()
Dim r As Range




With Worksheets("Product_Master")
Set r = .Cells(2, 2)
Set r = Range(r, r.End(xlDown))

aryProducts = Application.WorksheetFunction.Transpose(r)
End With

Me.tbSearch.Text = vbNullString


End Sub


In Textbox_Change, fill the Listbox (Listbox AddItem) with Products that start with what is in the Textbox



Private Sub tbSearch_Change()
Dim i As Long
With Me

.lbProduct.Clear

If Len(.tbSearch.Text) = 0 Then Exit Sub


For i = LBound(aryProducts) To UBound(aryProducts)
If UCase(Left(aryProducts(i), Len(.tbSearch.Text))) = UCase(.tbSearch.Text) Then
.lbProduct.AddItem aryProducts(i)
End If
Next i

End With


End Sub





Save/Process/Select the listbox item that is highlighted



Private Sub CommandButton8_Click()
With Me
If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value

.Hide
End With

Unload Me


End Sub

[Paul_Hossler]

Look at this line and insetad of MsgBox use .lbProduct.Value in your other places



If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value

[lordbodom]

Look at this line and insetad of MsgBox use .lbProduct.Value in your other places



If Not IsNull(.lbProduct.Value) Then MsgBox .lbProduct.Value


What would I do with cmb_Product? This is what the combobox was and a lot of code is associated with that? Just replace cmb_Product with tbsearch?

[Paul_Hossler]

Post #13

Yes

Replace cmb_Product and it's event code with tbSearch and lbProduct and the event code in my example.

Then in Commandbutton8_Click use lbProduct.Value for the follow on processing

[lordbodom]

Sorry, still a bit confused. For eg. have this:

Sub Refresh_Product_List()

Application.ScreenUpdating = False

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product_Master")

Dim i As Integer

Me.cmb_Product.Clear
Me.cmb_Product.AddItem ""
If Application.WorksheetFunction.CountA(sh.Range("A:A")) > 1 Then
For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
Me.cmb_Product.AddItem sh.Range("B" & i).Value
Next i
End If

End Sub


Here, theres calls to cmb_product here. ANd I believe more like this across other code. I understand removing cmbbox and adding listbox along with the new code. But still confused on these older references. Thank you.


Post #13

Yes

Replace cmb_Product and it's event code with tbSearch and lbProduct and the event code in my example.

Then in Commandbutton8_Click use lbProduct.Value for the follow on processing

[Paul_Hossler]

Here, there's calls to cmb_product here. And I believe more like this across other code. I understand removing cmbbox and adding listbox along with the new code. But still confused on these older references. Thank you.

If you replace Combobox with a Textbox and a Listbox, then all the uses of the Combobox will have to be changed accordingly

The Test button opens the userform, and the Search Textbox searches product names that match. Clicking a Product Name in the Listbox populates Qty and ID (example of using the selection)

28799


FWIW, I like to

1. Define Const values for key columns to make the code easier to interpret

2. Put all non-Userform initialization code in a separate sub

3. Use small subs/functions to improve readability




Option Explicit


Public aryProducts As Variant
Public aryID As Variant
Public rData As Range


Public Const colID = 1
Public Const colProduct = 2
Public Const colPrice = 3
Public Const colAvail = 4
Public Const colStock = 5


'========================================================================== ==============
Sub drv()


Init


Load frm_Inventory_Management
frm_Inventory_Management.Show
End Sub
'========================================================================== ==============




Sub Init()
Dim r As Range

With Worksheets("Product_Master")
Set r = .Cells(2, colProduct)
Set r = Range(r, r.End(xlDown))

aryProducts = Application.WorksheetFunction.Transpose(r)

Set r = .Cells(2, colID)
Set r = Range(r, r.End(xlDown))

aryID = Application.WorksheetFunction.Transpose(r)

Set rData = .Cells(1, colID).CurrentRegion
End With


End Sub




Function rowLookupByProduct(s As String) As Range
Dim r As Long

r = Application.WorksheetFunction.Match(s, rData.Columns(colProduct), 0)

Set rowLookupByProduct = rData.Rows(r)
End Function


Function rowLookupByID(s As String) As Range
Dim r As Long

r = Application.WorksheetFunction.Match(s, rData.Columns(colID), 0)

Set rowLookupByID = rData.Rows(r)
End Function