Vlookup table to find email addresses

I have a table (tblEmails) on a worksheet, It has two columns. First column named Subcontractors and second named email.

I have created a userform with two listboxes (lboSubcontractors) and (lboEmails). I want the code to find the email addresses for all the subcontractors in the listbox and show them in the second listbox.

I am not sure how to go about this but I would say a loop to vlookup each name in the listbox?

I'd really appreciate any help with this!

How are you populating the Subcontractors listbox? Is it all the values in the table or just some?

There is over 100 company names in the table and only some will be selected each time. The subcontractors listbox names will be identical to the names in the table. I just need it to return the email address for each name in the listbox

Thanks

Can you post the code that loads the listbox?

If ckboxAcoustics.Value = True Then lboSubcontractors.List = Array("Commercial Connections", "Art of Acoustics", "Artworks")

I have a table on my worksheet that has the email addresses for each of them company's. But can you vlookup the table with each company as the reference and return their emails in another listbox?

Thanks

Ok, thanks for that.
How about

   Dim Fnd As Range
   Dim Ary As Variant, Nary As Variant
   Dim i As Long
   
   Ary = Array("Commercial Connections", "Art of Acoustics", "Artworks")
   ReDim Nary(UBound(Ary))
   If Me.ckboxAcoustics.Value Then
      Me.lboSubcontractors.List = Ary
      For i = 0 To UBound(Ary)
         Set Fnd = Range("tblEmails").Find(Ary(i), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then Nary(i) = Fnd.Offset(, 1).Value
      Next i
      Me.lboEmails.List = Nary
   End If

sorry, I should've mentioned that is only one way it will be populated. I have 40 checkboxes and depending on which one is checked determines the subcontractors (array) that populates the listbox. So each time a checkbox is checked the listbox will be updated with new names which is why I was thinking of using vlookup type thing.

Thanks for your help btw

Assuming that code is run on a checkbox_Click event, you could use the same structure for each chkbox.

does that mean I need to do that code 40 times?. The checkboxes will work on a click event to update the subcontractors listbox but the email listbox will work off a listbox click event if that makes sense? Sorry I'm quite new to vba so thanks for your time

My process is I have 40nr Checkboxes for each type of trade on a userform ie Scaffolding Glass Stone Metal etc. A user will click the checkboxes for each type of trade required for that job.

If selected a listbox for that trade will get populated with the relevant company names. In my code this listbox is named lboBuilderswork. using code (If ckboxBuilderswork.Value = True Then lboBuilderswork.List = Array("Trade1", "Trade2", "Trade3")

The lboEmailTrades listbox gets populated with a list of all the trades selected when clicking the checkboxes. So from there Fluff13's code works perfect but I will need 40 different scenarios of it for the 40 trades;

Private Sub lboEmailTrades_Click()
   Dim Fnd As Range
   Dim Ary As Variant, Nary As Variant
   Dim i As Long
   
   If Me.lboEmailTrades.Value = "Builderswork" Then
      Me.lboSubcontractors.List = lboBuilderswork.List
      ReDim Nary(UBound(lboBuilderswork.List))
      For i = 0 To UBound(lboBuilderswork.List)
         Set Fnd = Range("tblEmails").Find(lboBuilderswork.List(i), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then Nary(i) = Fnd.Offset(, 1).Value
      Next i
      Me.lboEmailList.List = Nary
   End If

I hope this makes a bit of sense, sorry I cant post my actually file atm

Thanks

I edit the 40 listboxes sometimes to add more new contacts in or out depending on job location etc. so will that way still work?

I will make up a quick sample File to try to show you what I mean

Please see attached sample file

Thanks

How about change the chkbox events to

Private Sub ckboxBuilderswork_Click()
   Dim Ary As Variant
   Ary = Array("A", "B", "C")
If ckboxBuilderswork.Value = True Then
   lboBuilderswork.List = Ary
   Call ListBxFill(Ary)
Else
   lboBuilderswork.Clear
   Me.lboEmailList.Clear
End If
End Sub

and put this in the userform module

Sub ListBxFill(Ary As Variant)
   Dim Fnd As Range
   Dim Nary As Variant
   Dim i As Long
   
   ReDim Nary(UBound(Ary))
   For i = 0 To UBound(Ary)
      Set Fnd = Range("tblEmails").Find(Ary(i), , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then Nary(i) = Fnd.Offset(, 1).Value
   Next i
   Me.lboEmailList.List = Nary
End Sub

fluff13 thanks very much for this.

Few questions

Would I need to repeat this code for all 40 checkbox click events?

And my array is initially just to populate the listbox with my usual subcontractors, then I will use the lbobuilderswork listbox etc to add new subcontractors in depending on the job. Therefore array needs to equal the items in lbobuilderswork.

Originally Posted by colin7

Would I need to repeat this code for all 40 checkbox click events?

Yes you will, but only the first code, the second code remains as-is.

When you add the extra subcontractors, you will need to modify the code to add the extra emails as well.

Thanks very much for your help, really appreciated.

Would you know a way to modify the code to suit adding more in?

Thank you

You're welcome & thanks for the feedback.

As your new request is totally different, you will need to start a new thread & include your existing code.

Returned home and noticed this still active.
I see a number of your other queries have been answered.
However if this is still ongoing my partial solution is attached.
Adding to the list automatically expands the scope as there are no 'hard coded fields' to search.
torachan.

Hi torachan,

Thanks for your reply!

I think your code will work brilliantly. I will try to incorporate your code into my file and see how it goes, thank you