listbox populated with Vlookup from multiple worksheet

[jay6939]

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.

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.

[DocAElstein]

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

[jay6939]

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.

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

thanks

[DocAElstein]

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

[jay6939]

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

Code that works,

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

[DocAElstein]

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