listbox populated with Vlookup from multiple worksheet

Hello VBA Guru,
i am newby to VBA.i have Excel file with 3 user forms (file attached). i have managed to work out most of functions by learning VBA through different forums and youtube. i am stuck to create below formula that will finish my form.
looking for help to create formulas for (i have tried many but nothing works)
1) copy selected items from listbox 1 (lstdatabase, userform3) to listbox 2 (lstdatabse1, useform1). i am able to copy required column from lstdatabase to lstdatabse1. however, i would like to add additional column to lstdatabse1(userform1) from different sheet (cost, cost1 , cost2) in workbook by referencing part number column 3 of lstdatabse1. i have tried Vlookup but somehow it not working (highlighted in red). please refer below code i used for this extercise. please suggest modification or provide your input to make it work.

Code:

---

Private Sub cmdcostupdates_Click()
With UserForm1.lstdatabase1

        .ColumnCount = 10
        .ColumnHeads = True
        .ColumnWidths = "40,60,60,60,60,100,100,250,80,80"


Dim i As Integer
For i = 0 To UserForm3.lstDatabase.ListCount - 1

    If UserForm3.lstDatabase.Selected(i) = True Then
    UserForm1.lstdatabase1.AddItem
    UserForm1.lstdatabase1.Column(0, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(0, i)
    UserForm1.lstdatabase1.Column(1, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(1, i)
    UserForm1.lstdatabase1.Column(2, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(2, i)
    UserForm1.lstdatabase1.Column(3, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(3, i)
    UserForm1.lstdatabase1.Column(4, (UserForm1.lstdatabase1.ListCount - 1)) = UserForm3.lstDatabase.Column(4, i)
    'UserForm1.lstdatabase1.Column(6, (UserForm1.lstdatabase1.ListCount - 1)) = Application.WorksheetFunction.VLookup(UserForm1.lstdatabase1.Column(3, i), Sheets("cost").Range("A1:G1000"), 7, False)
    'UserForm1.txtcurrentprice3.Value = Application.WorksheetFunction.VLookup(UserForm1.lstdatabase1.Value("Selection.Row;3"), Sheets("cost").Range("A1:G1000"), 7, False)
    End If
Next i

 
    UserForm1.Show
End With

End Sub

---

thanks if advance for your time and help.

i have posted this on other forum and pending any help.

https://www.mrexcel.com/board/thread...ookup.1139401/
https://www.dropbox.com/preview/Exce...?role=personal
2) once, lstdatabse1 populated with column 4 (from cost tab),5 (from cost1 tab) and 6 (from cost2 tab), i would like to click on particular row in lstdatabase1 and userform1 textbox should automatic fill from values.

Hi jay6939
Welcome to ExcelFox
Sorry you’ve got no reply. There are not so many helpers looking in just now at excelfox so it may be a while before someone with UserFrom experience passes by. I don’t have much experience with UserForms, but I did try to have a quick look.
But there seems to ba a problem with the link to your file, both here and at mrexcel.com. It appears to go to a log-in page, or similar, and there are no indications of a share link to the file. This is the link you gave
https://www.dropbox.com/preview/Excel/Part%20tracker%20test.xlsm?role=personal
This is where that link takes you: https://imgur.com/C3ws8vR
Possibly you have given us the wrong link in error? Usually you can get a “share” link to pass on, once you have enabled share for the file at your dropbox account.

You can also upload a file at excelfox. It is not obvious how to do that. Here are some notes on that
https://excelfox.com/forum/showthrea...ll=1#post14273

I have also edited your post for you, since the BB code color tags that you used in your coding messed up those highlighted code lines: You used [COLOR=rgb(184, 49, 47)] , which does not seem to work here. Possibly that was copied from mrexcel where they have a quirky new software. I replaced that with [COLOR=red]
I also wrapped your code in code tags ( https://excelfox.com/forum/showthrea...ll=1#post14274 )


Good luck
Alan

Thanks for reply DocAElstein.
i have updated link for sample file.
i have tried below Vlookup as well. but system gives me error of no mach type.

Code:

---

UserForm1.lstdatabase1.Column(5, (UserForm1.lstdatabase1.ListCount - 1)) = Application.VLookup(UserForm1.lstdatabase1(.List(.ListIndex, 5), Sheets("Cost").Range("A1:G1000"), 7, False))

---

thanks

Hi jay6939,

Your link has not chenaged. It is still taking me to a log in page.
https://www.dropbox.com/preview/Excel/Part%20tracker%20test.xlsm?role=personal
It is not a link to a file

We need the share link,
or
upload a file

Alan

DocAElstein,
thanks for response. i have found solution on Mrexcel.

Code that works,

Code:

---

Private Sub cmdcostupdates_Click()
  Dim i As Long, n As Long, f As Range
 
  With UserForm1.lstdatabase1
    .ColumnCount = 10
    .ColumnHeads = True
    .ColumnWidths = "40; 60; 60; 60; 60; 100; 100; 250; 80; 80"
    For i = 0 To UserForm3.lstdatabase.ListCount - 1
      If UserForm3.lstdatabase.Selected(i) = True Then
        .AddItem
        n = .ListCount - 1
        .Column(0, n) = UserForm3.lstdatabase.Column(0, i)
        .Column(1, n) = UserForm3.lstdatabase.Column(1, i)
        .Column(2, n) = UserForm3.lstdatabase.Column(2, i)
        .Column(3, n) = UserForm3.lstdatabase.Column(3, i)
        .Column(4, n) = UserForm3.lstdatabase.Column(4, i)
        Set f = Sheets("cost").Range("A:A").Find(.Column(3, n), , xlValues, xlWhole)
        If Not f Is Nothing Then
          .Column(6, n) = Sheets("cost").Range("G" & f.Row)
        End If
      End If
    Next i
    UserForm1.Show
  End With
End Sub

---

Thanks for letting us know and thanks for posting the solution
Alan