Hi Guys
I have a search userform which can dynamically search for items and display them in a list box. I would like it so that when I select an item from the list box and click view it will bring this items data into another userform I have created which will populate all the relevant textboxs
Here is the code I have so far for the search user form which searches the product from my main table "MasterProducts". I have another userform called "ProductControl" of which I would like the selected item to be displayed in that userform.
Code for Search userform with list box
Here is the userform_initalize code from the ProductControl Userform.
The lines of code below I am not sure if relevant but is the PopulateForm part from the above code
So basically when an item is selected from the list box in the search userform, it opens the productcontrol userform and populates this with data from the "MasterProducts" table. Also if the ProductControl userform can be initialized with other selections from other userforms would be good, as that will more than likely what I'll try and do if I can suss this one out.
I'm still a newby at all this and have spent months learning and gathering different code, splicing it together until I get the desired results. So I apologise in advance if I've copied and pasted far too much unnecessary information. I am using Office 365. Any help would be great!
Cheers
I have a search userform which can dynamically search for items and display them in a list box. I would like it so that when I select an item from the list box and click view it will bring this items data into another userform I have created which will populate all the relevant textboxs
Here is the code I have so far for the search user form which searches the product from my main table "MasterProducts". I have another userform called "ProductControl" of which I would like the selected item to be displayed in that userform.
Code for Search userform with list box
VBA Code:
Dim dTable As ListObject
Dim ws As Worksheet
Dim tRow As Long
Dim lstRow As Long
Dim dString As String
Private Sub Init_Listbox()
Set ws = Worksheets("Master Products")
Set dTable = ws.ListObjects("MasterProducts")
lstRow = dTable.DataBodyRange.Rows.Count
With Me.ListBox1
Me.tbxRec.Value = 0
.Clear 'clear listbox
.ColumnCount = 5 'Set nr of columns
.ColumnWidths = "0;120;350;90;150" 'set the column widths
For tRow = 1 To lstRow 'start filling the listbox
'dstring will hold the data of the three columns in one string as lower case text used for searching
dString = LCase(dTable.DataBodyRange(tRow, 1).Value & dTable.DataBodyRange(tRow, 2).Value & dTable.DataBodyRange(tRow, 7).Value & dTable.DataBodyRange(tRow, 9).Value)
' the select statement checks the length of the search text
Select Case Len(Trim(Me.TextBox1.Value))
Case Is > 0 '* if its greater than 0 it checks if the text is present in the dstring if not record is skipped
If InStr(1, dString, LCase(Me.TextBox1.Value)) = 0 Then GoTo nexttRow
End Select
.AddItem
.List(.ListCount - 1, 0) = tRow '* record's row number
.List(.ListCount - 1, 1) = dTable.DataBodyRange(tRow, 1).Value '* Sku
.List(.ListCount - 1, 2) = dTable.DataBodyRange(tRow, 2).Value '* Title
.List(.ListCount - 1, 3) = dTable.DataBodyRange(tRow, 9).Value '* Supplier Code
.List(.ListCount - 1, 4) = dTable.DataBodyRange(tRow, 7).Value '* Supplier Name
Me.tbxRec.Value = .ListCount '* updates the record counter showing the nr of records in the list
nexttRow:
Next tRow
End With
End Sub
Here is the userform_initalize code from the ProductControl Userform.
VBA Code:
Sub UserForm_Initialize()
Set MasterProductTable = ActiveSheet.ListObjects("MasterProducts")
'Initialise for empty table
ChangeRecord.Min = 0
ChangeRecord.Max = 0
CurrentRow = MasterProductTable.ListRows.Count
If CurrentRow > 0 Then
ChangeRecord.Min = 1
ChangeRecord.Max = MasterProductTable.ListRows.Count
'Load last record into form
PopulateForm MasterProductTable.ListRows(MasterProductTable.ListRows.Count).Range
MasterProductTable.ListRows(MasterProductTable.ListRows.Count).Range.Select
UpdatePositionCaption
Else
RecordPosition.Caption = "0 of 0"
End If
End Sub
The lines of code below I am not sure if relevant but is the PopulateForm part from the above code
VBA Code:
Private Sub PopulateForm(SelectedRow As Range)
With SelectedRow
TitleTxt.Value = .Cells(1, 2).Value
SKUtxt.Value = .Cells(1, 1).Value
SupplierCodeTxt.Value = .Cells(1, 7).Value
PurchasePriceTxt.Value = .Cells(1, 17).Value
BarcodeTxt.Value = .Cells(1, 9).Value
WidthTxt.Value = .Cells(1, 13).Value
DepthTxt.Value = .Cells(1, 14).Value
HeightTxt.Value = .Cells(1, 15).Value
WeightTxt.Value = .Cells(1, 16).Value
Brandtxt.Value = .Cells(1, 45).Value
ColourTXT.Value = .Cells(1, 84).Value
DescriptionTXT.Value = .Cells(1, 79).Value
Min_Leveltxt.Value = .Cells(1, 27).Value
MinUKFBAtxt.Value = .Cells(1, 28).Value
MinUSFBAtxt.Value = .Cells(1, 29).Value
SupplierNameList.Value = .Cells(1, 8).Value
PackagingList.Value = .Cells(1, 11).Value
Categorylist.Value = .Cells(1, 5).Value
Category2ndList.Value = .Cells(1, 36).Value
Category3rdList.Value = .Cells(1, 37).Value
WeightHandlingList.Value = .Cells(1, 25).Value
PrefixList.Value = .Cells(1, 26).Value
CountryofOriginList.Value = .Cells(1, 40).Value
HarmonisedCodeList.Value = .Cells(1, 41).Value
If .Cells(1, 76) = "Yes" Then
IncludeInWebsiteCheck.Value = True
Else
IncludeInWebsiteCheck.Value = False
End If
End With
End Sub
So basically when an item is selected from the list box in the search userform, it opens the productcontrol userform and populates this with data from the "MasterProducts" table. Also if the ProductControl userform can be initialized with other selections from other userforms would be good, as that will more than likely what I'll try and do if I can suss this one out.
I'm still a newby at all this and have spent months learning and gathering different code, splicing it together until I get the desired results. So I apologise in advance if I've copied and pasted far too much unnecessary information. I am using Office 365. Any help would be great!
Cheers
Last edited by a moderator: