Fill a ListBox-control with values from another workbook

Posted on 2009-04-24
Last Modified: 2013-12-26
I have a combo box (list box) named Vehicle_Type. I would like to get info from workbook C:\Users\Owner\Desktop\Real Indy\FilterApplication.xls  but I will be on another sheet on another workbook.  
Question by:dave5m
    LVL 33

    Accepted Solution

    There are various ways this could be done.

    1 Use code to extract values from a closed workbook.

    2 Open the workbook with the data, then populate the listbox using AddItem.

    3 Copy the data to a hidden worksheet in the workbook with the listbox, and then populate in the usual ways eg AddItem, List, named ranges etc.

    1 is possible but could be complicated - I must admit most of the code I've seen to get data from a closed workbook has involved only a few values.

    Wth 2 &3 you would need to open the workbook, but the user doesn't need to see that happening.

    Dim wbData As Workbook
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'open workbook
    Set wbData = Workbooks("C:\Users\Owner\Desktop\Real Indy\FilterApplication.xls")
    ' code to populate listbox, what this is depends on at least 2 things - where the listbox located eg worksheet/userform and what type of control it is if the latter. ie worksheet

    Open in new window


    Author Comment

    i finally see what you mean.  i'm a little slow.  now one question.  after i want to close the sheet that i copied and pasted using a macro to close a sheet, excel asks me if i want to save the sheet. i do not but, there will be other users using this program and i do not want excel to ask them either.  can i take that message box out?
    LVL 33

    Expert Comment

    Well you don't 'close' a worksheet.

    You can however close a workbook, as the code does in line 12.

    If you add False after Close the user should get no message and the file won't be saved.

    If you need to suppress other messages you can use something like this.
    Application.DisplayAlerts = False

    ' your code

    Application.DisplayAlerts = True


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now