vba code to populate combobox from another workbooks (excel 2007)

  • Hi guys,



    i know this has been done to death. but i still cannot digest it. I have done hundreds of search in the last month, youtube, etc but maybe coz i am not bright enough, and i am a noob at VBA, i cannot make it working.



    Here is what i am trying to do: I want to populate comboboxes in main.xlsm from a range in another workbooks (name.xlsx and city.xlsx).


    main.xlsm has 2 comboboxes. combobox 1 will contain value/range from workbooks name.xlsx (b2:b6), and combobox 2 will contain value from workbook city.xlsx (a2:a5).




    I have tried some code, including the one i seen in this forum.



    e.g.


    ThisWorkBook





    but none is working.



    is anyone here kind enough to show me how to accomplish this? many thanks!

  • Re: vba code to populate combobox from another workbooks (excel 2007)


    What I assume you have is a combobox on a sheet in Main.xlsm - you want to populate it with the data from name.xlsx Range B2:B6 - and then also another combobox with data from city.xlsx. I am just going to show you how to do the first part - populate a combobox on Main.xlsm with data from name.xlsx. I assume you want this done when Main.xlsm is opened as you have your sample code in the workbook open event. This code will open name.xlsx and get the data from the range, populate the combobox and then close name.xlsx. You will need to follow the example to do the same with the other combobox.


    Attached is a sample workbook - code is in "This Workbook".


    It is faster to use the List property rather than iterating through the array and adding each item one at a time with additem property however the data range is pretty small anyway

  • Re: vba code to populate combobox from another workbooks (excel 2007)


    Code
    Private Sub Workbook_Open() 
         Sheet1.Combobox1.List=Getobject(thisworkbook.path & "\name.xlsx").sheets("sheet1").Range("B2:B6").Value
         Sheet1.Combobox2.List=Getobject(thisworkbook.path & "\city.xlsx").sheets("sheet1").Range("A2:A5").Value
    End Sub
  • Re: vba code to populate combobox from another workbooks (excel 2007)


    snb - very cool - the workbooks don't even need to be open for your method to work - very succinct.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: vba code to populate combobox from another workbooks (excel 2007)


    Sorry - worded my response badly - obviously need to be opened to get data - doesn't detract from fact that still cool method :)

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: vba code to populate combobox from another workbooks (excel 2007)


    Thanks snb, that indeed is a very compact code and works!



    if i do not know the number of data in name.xlsx and city.xlsx (e.g. range can change from time to time, next time maybe from B2:B20), how can i still display it all?

  • Re: vba code to populate combobox from another workbooks (excel 2007)


    Please do not quote !


    Code
    Private Sub Workbook_Open() 
        Sheet1.Combobox1.List=Getobject(thisworkbook.path & "\name.xlsx").sheets("sheet1").Range("B2").currentregion.Value 
        Sheet1.Combobox2.List=Getobject(thisworkbook.path & "\city.xlsx").sheets("sheet1").Range("A2").currentregion.Value 
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!