+ Reply to Thread
Results 1 to 16 of 16

Filling multiselect listbox based on closed workbook data

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    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.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Filling multiselect listbox based on closed workbook data

    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.

  3. #3
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626
    What data will vanish when you close the workbook?
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Filling multiselect listbox based on closed workbook data

    Sorry that doesn't sound like data is disappearing.

    It sounds more like the data wasn't there in the first place.

    If the problem is caused by the 2nd workbook being closed before you show the userform then why are you closing it then.

    Why not try closing the workbook after the userform has been shown and the listboxes populated?

  7. #7
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Filling multiselect listbox based on closed workbook data

    How did you try closing the workbook in the userform Initialize event?

  9. #9
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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 = ""

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Filling multiselect listbox based on closed workbook data

    Can you post the entire code for the userform's Initialize event?

    Also, are you using RowSource for any controls on the userform?

  11. #11
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Filling multiselect listbox based on closed workbook data

    How were you populating the listboxes if you weren't using the Initialize event?

    That's kind of the logic place to do it.

    Anyway, here's how you can upload an example workbook.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  13. #13
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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.

  14. #14
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Filling multiselect listbox based on closed workbook data

    I moved all the code in the sub AdicOPs to the userform Initialize event and only left this.
    Please Login or Register  to view this content.
    Everything ran fine, though the 2nd listbox wasn't populated.

    PS You can refer to the workbook with the code using ThisWorkbook, so you could replace Workbooks("Plano Mestre de Produção Fibralit DCF-09.xlsm") with ThisWorkbook throughout the code.

  15. #15
    Registered User
    Join Date
    08-22-2013
    Location
    Campinas, Brasil
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Filling multiselect listbox based on closed workbook data

    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.

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626
    No problem.

    You could put code to populate controls in a userform but it's better to have it in the Initialize event.

    If you step through, with F8, the original code you might see one reason why.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] MultiSelect Listbox in Userform to copy data to worksheet
    By aarodn in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-06-2013, 03:52 PM
  2. Copy Selected items from multicolumn, multiselect listbox to another listbox
    By Willigb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2013, 11:27 AM
  3. Use a Multiselect listbox to filter data
    By EoghanMBH in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2012, 07:21 AM
  4. Multiselect listbox result and dynamic chart based on that
    By pam79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2012, 04:00 AM
  5. Multiselect Listbox use
    By RKS in forum Excel General
    Replies: 1
    Last Post: 05-12-2006, 10:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1