Listbox selection to a different workbook

[pprseller]

I was fortunate to get help on the listbox code but need to direct the selection to a different workbook than the one that contains the listbox. I'm not good enough to see what I'm doing wrong. Played around with different combos of selecting the workbook but can't get it to work. Thanks for the help. Amazing knowledge in this group.

Please Login or Register  to view this content.

There are several more lines of code but the issue seems to be on the workbook selection. Thank you!

[antoka05]

Try to replace this code:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

if you want get data from active sheet and put into another workbook's sheet.
Regards,
Antonio

[royUK]

The code is in the workbook contaig the listBox, this is rferred to in VBA as ThisWorkBook.

Where are you writing to, why a different workbook?

[pprseller]

Hi RoyUK, Helping me again. Thank you!

I think I have to write to another workbook. The order form that will receive the address information from the list box is updated continuously. This order form has 10K products on it. (I took the links off when I sent you the dummy form.) I update this form, sometimes daily, and send it to the users.

The "addressbook" will be generated by 8 sales reps. These will be personal to each person's territory. I changed your code so that the listbox is populated with the user's selected (active) sheet in the addressbook. That works fine. Now I just need it to direct the listbox selection to the workbook(dataform) onto worksheet(order).

Is there another option that I'm not seeing? I work with non-Excel users so it has to be very easy. Thank you!

[royUK]

can you have the form template in the master book & create a copy hen completed?

[pprseller]

I think I understand what you're getting at. But, I don't know enough about templates and code to know if it would work. The form is a VBA project with 29 sheets, 30 forms and 6 modules. Can that be made into a template? If so, can that template be easily imported by the users into their address workbook after changes are made?

[royUK]

I'm not clear exactly how your program is designed.

You have a workbook with the listbox in. Is the form in another workbook that will be open, if so will this be the active workbook?

[pprseller]

User will open workbook("orderform") to put the products on the order form. The list box code is on the AddressBook workbook so I have them open the workbook("addressbook") with this code:

Please Login or Register  to view this content.

I think this just opens the address Workbook. Can we activate the address Workbook at this point? If the address book is activated, can we direct the listbox selections to an inactive book(the orderform workbook)? Is it that you can't have two workbooks active at the same time?
Thanks for your patience.

[royUK]

That code opens a new instance of Excel, not ideal for what you want.

I'll put some code together for you to try later.

[pprseller]

Thank you. I appreciate your help very much.

[royUK]

Try this, yo need to be sure the correct file is in the code & the correct sheet

Please Login or Register  to view this content.

[pprseller]

Thank you once again. Slight problem that I can't fix. I changed the file path and the sheet. Like this:

Please Login or Register  to view this content.

This stops at Sheet2. I know that's the right sheet. The error code says:
"Object doesn't support this property or method." Any ideas?

[trucker10]

Remove the Option Explicit at the beginning of the code

[royUK]

Remove the Option Explicit at the beginning of the code

Why??? It's good practice o always use Option Explicit, the only time to delete it would be if it was there twice by mistake.

pprseller

Can you attach your new workbook with the code in

[pprseller]

Thanks for sticking with me on this. This isn't doing what I need. This is opening the "dataform" from the "addressbook". I need it the other way around. We won't always be needing the address book and the dataform is linked to a database that we use. So, in summary, this is what I am trying to do:
A user opens the dataform.
If needed, they open the address book.
They choose a site from one of the worksheets using a list box (or we can change that to whatever is best). (I don't know how many worksheets each user will have - the code for the listbox needs to work on active worksheet.)
The listbox selection is entered onto the dataform.
The dataform is printed but not saved.

I've attached both workbooks. Any help is appreciated. Thank you!
AddressBook.v.1.xls

Dataform.test.xls