Populate another userform with a selected item from listbox

[jamobe]

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

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.

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

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

 

[diddi]

hi. i have not looked over the whole post, but when i read the first part, it reminded me of how i used to code. then i made a discovery that makes life much easier imo. the problem you will encounter when having 2 windows open at once is that you seem to be constantly juggling variables between the 2 userforms, which i inevitably messed up. so, what i do instead is to place a frame over the entire userform and use it as a second layer on the same form, so there are no problems any more. initially when getting it all going, make your window giant so you can see the normal layer and your new frame1 layer next to it. now, just program as usual. all your problems disappear. use Frame1.Visible to switch between layers