We have 4000 different materials/equipment in our stock.
We use a VBA stock macro, integrated with a barcode scanner, to make all the stock process.
We summarize all the different materials/equipment in another workbook separately thanks to VBA codes (let's say Summary Workbook).
To see how many different pipes and how many meters of pipes we have in our stock, you should click "PIPES" sheet inside of Summary Workbook.
For "ELECTRICAL MATERIALS", "FLANGES", "FITTINGS", "ASSETS" and the nearly 20 other stock groups it is the same.
All the titles are separated and they are all the different pages as a list.
Also I list all the titles ("ELECTRICAL MATERIALS", "FLANGES", "FITTINGS", "ASSETS", "PIPES" etc.) to another sheet (let's say DATA Sheet).
The main idea is: Use this sheet as a data list.
All the above operations purpose is checking materials/equipment quantity easily and how many different products we have in our stock. But when you open the "Summary Workbook" it is complicated to check. Every stock group includes at least 150 different materials/equipment.
So I created another sheet in Summary Workbook and named it Main Sheet. Besides, I created a textbox and a Listbox in it.
I choose all the stock information inside of the DATA sheet from (A2:F4214) and named them "DATA".
So when I choose the Listbox on the main sheet, I transfer all the "DATA"'s using "ListFillRange" method.
I use 6 columns with headings.
1- Number
2- Barcode No.
3- Stock Group Name
4- Stock Name
5- Stock Quantity
6- Stock Measurement (Meter, Piece, Set, Liter etc.)
Code to use textbox as a Search Box:
Private Sub TextBox1_Change()
Dim i As Long
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, 1)
Me.ListBox1.Clear
For i = 2 To Application.WorksheetFunction.CountA(Sayfa281.Range("D:D"))
a = Len(Me.TextBox1.Text)
If Sayfa281.Cells(i, 4).Value Like "*" & TextBox1.Text & "*" Then
Me.ListBox1.AddItem Sayfa281.Cells(i, 4).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 4) = Sayfa281.Cells(i, 4).Value
End If
Next i
End Sub
It gave:
Run time error '- 2147467259(80004005)':
Unspecified error.
When I click DEBUG, it shows Me.ListBox1.Clear in yellow.
When I used the above code inside of a user form it worked, but in an Excel sheet, it did not.
For the listbox shrinkage bug, you can do the following.
ListBox1.Width = 1000
ListBox1.Height = 800
just before leaving the sub. It worked for me.