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?

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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;

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

and put this in the userform module

Please Login or Register  to view this content.

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