Filling multiselect listbox based on closed workbook data

Hey all,

I am having a problem with the initial part of a project I'm developing. As the whole project is a bit big I will try to be the most specific I can.

So, here is my problem. I have a Workbook called Plano Mestre de Produção Fibralit DCF-09, with a worksheet containing a 28 columns simple table, and a module which is carrying all the code. I also have another Workbook called Banco de Dados Produção Fibralit, a database kind of workbook, which will absorb and read some of the information during the code process.
I created two Multiselect Listbox that will be filled with some data based on some parameters. Those listbox will read the data from the Plano Mestre de Produção Fibralit DCF-09 workbook, which will be always open as it holds the code, and from the Banco de Dados Produção Fibralit workbook. The second, however, will be closed at all the time, and should only be opened to help fill the listbox and then closes again.
The problem is, the code works perfectly if both the workbooks are opened and keep opened. However, if the code opens the second workbook at the beginning of the procedure and closes it at the end before the userform is called, both the listboxes get blank. What should i do do open the second workbook at the beginning of the procedure and close it at the end without losing the data at the userform show?

Any help or other suggestions are welcome.

The code is as follows:

Thank you.

Please Login or Register  to view this content.

Hi

If I were you i would copy the data on the closed workbook onto a sheet on your main workbook.

Then I would use that to populate my listbox.

The method works for normal and for multi column list boxes.

The following reads H8 from a closed workbook

Please Login or Register  to view this content.

The following code Populates Listbox 5 using a horizontal list it calculates the number of columns to use by looking for the first blank on the row.:-

Please Login or Register  to view this content.

if the data is stored vertically then you do not need to use "application.Transpose"

Hopefully that will get you started.

Hi mehmetcik,

Thank you for your reply.

I am also thinking that copying the data from the closed workbook to the main workbook is the only and easiet way out of that problem. I could do that as a link formula to the closed workbook, but i think it would get a little bit slow to calculate in the future, as we are talking about a database kind of workbook that will be daily fed with data. Or i could just copy and paste the values to the main workbook every time the database gets new data to be read.

The ExecuteExcel4Macro method is also a useful option. However, in my case, i think i would need to loop through the references i am analyzing and then change the string argument every time to get the values i want. Maybe this will get the slowest result.

The open and close workbook would be perfect if not for the data vanish after the close method.

Thank you for all the tips.

Hey Norie,

What happens is the following,

The code works perfectly if both the workbooks are opened and keep it that way after the userform show. However, if i choose to open the second workbook just to use it as a reference to some parameters I need to check between both, and then close it right before the userform show I get blank listboxes.
The data that was supposed to go to the listbox is actually in the main opened workbook where the code is written, but just because i use that second workbook as a comparison reference during the loop to add items to the listbox, if i close it before the listbox show i get no data at all.

I also can't understand why that happens.

And the strange is that the data populates both the listboxes during the process. If I Debug.Print the Listboxes.List references right before the workbook.close method it shows the results as expected. If I do it after, i shows Null.

The code works perfectly if i put the close method after the userform.show. However, it will only actually close the workbook after i close userform itself. That wouldnt be a big deal as I could hide the workbook or something, but during that time there is a big chance other people will be using other parts of the system and will need to get access to the original database and feed it with new data.

I also just tried closing it during the initialize or activate userform events. The result was that in the initialize i got an error and in the activate it opens and instantly closes the workbook AND the userform.

By the usual method: Workbooks("Banco de Dados Produção Fibralit.xlsx").Close savechanges:=False

I got the Runtime error 91 - Object variable or With block variable not set. The debug jumps to the following code line: UserForm2.ListBox1.RowSource = ""

I wasn't using the initialize event until now. The only code it has right now is that workbook.close method.
I am not using rowsource for any of the controls I created either.

Anyways if you want me to send you the workbooks containing the code or only the codes for the whole userform controls I can post it here any time.

I was populating it through the code I posted which is into the main workbook module. It was working just as expected until I got stucked with that close issue. Is there anything I am missing?

Here goes the files:
Plano Mestre de Produção Fibralit DCF-09 is the main workbook, holding the codes and the userform. It already has 6 rows of data for you to play with it and test it.
The expected results after running the macro is the listbox1 populated with three columns of those six data rows. The second listbox will be empty at first.
Banco de Dados Produção Fibralit is the "database" which will only contain values.

Feel free to play with it.

Hi Norie,

Wow. That simple? Really?

I didn't even realized I should put it all into the initialize event and it's so obvious now.

I just tested it here and it really worked perfectly. Thank you very much. This is actually one of my first userforms and I don't know why but I thought that putting the code into a module was the only way to populate the listboxes.

Anyways, thank you very much for your help and attention to my problem. I think I would never have guessed that.

And thank you for the ThisWokbook tip either.