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

baberosa · Feb 1st 2014

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

Code

Private Sub Workbook_Open()




Dim oWbSource As Workbook
Dim rSource As Range








With Sheet1.ComboBox1
.Clear




Set rSource = Application.Workbooks("name.xlsx").Worksheets("Sheet1").Range("b2:b6")




.ComboBox1.RowSource = rSource.Address(external:=True)
.ListIndex = -1
End With


End Sub

Display More

but none is working.

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

smuzoen · Feb 1st 2014

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.

Code

Private Sub Workbook_Open()
Dim wbName As Workbook
Dim wsName As Worksheet
Dim rngArr As Variant
Dim cbBox1 As ComboBox


Application.ScreenUpdating = False
Set cbBox1 = Sheet1.ComboBox1
'change this to location of name.xlsx
Set wbName = Workbooks.Open("C:\Temp\name.xlsx")
'change this to the location of the data in name.xlsx
Set wsName = wbName.Worksheets("Sheet1")
'put the data in name.xlsx range into 1 Dimensional array
rngArr = Application.Transpose(wsName.Range("B2:B6"))
'Use list property to add the array of data into the combobox
cbBox1.List = rngArr
'close name.xlsx worbook
wbName.Close False
Application.ScreenUpdating = True
End Sub

Display More

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

snb · Feb 1st 2014

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

smuzoen · Feb 1st 2014

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.

snb · Feb 1st 2014

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

Well, they are open but not visible.

smuzoen · Feb 1st 2014

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

baberosa · Feb 8th 2014

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?

snb · Feb 8th 2014

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

baberosa · Feb 8th 2014

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

Hi snb, they both grabbed all values from A column (A1 to the end of data).

snb · Feb 8th 2014

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

In that case you will have to analyse and adapt my suggestion.

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

Participate now!

Share