ComboBox fill problem

chris115

New Member
Joined
Sep 29, 2006
Messages
9
Hi,

I currently have a combobox on a worksheet that I want to fill with a list of suppliers.

The list of suppliers are contained in a column on another workbook.

I have used the workbook_open() function to open the supplier workbook when the other workbook opens up.

I have changed the ListFillRange property of the combobox to fill it correctly, however when I close both files, the next time I open them the combobox is empty.

How do I code it so it fills every time I open the file?

Thanks in advance
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi chris115,

Do you want the combobox to be refilled from the list of suppliers in the supplier workbook each time the other workbook opens? Or do you want the other workbook to remember the supplier list once the combobox has been loaded with it so that the next time the workbook opens the supplier list will still be there?

Damon
 

chris115

New Member
Joined
Sep 29, 2006
Messages
9
Ideally, I want the combobox to be refilled from the list of suppliers in the supplier workbook each time the other workbook opens up.

I'm not sure how to load the data into the combobox from another workbook.

Thanks
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi again Chris,

In that case I recommend that you add code right in the Workbook_Open() procedure to load the list from the other workbook into a worksheet range somewhere in the workbook containing the combobox (ThisWorkbook). That way, the other workbook can be closed and so long as the list in the ThisWorkbook is intact the combobox will continue to have the updated list. You can simply assign the ListFillRange property of the combobox to the range where the list gets stored when the workbook opens.

To load the list from the other workbook to ThisWorkbook, you could use code like this:

Code:
Workbooks("Suppliers.xls").Sheets("SupplierList").Range("A2:A50").Copy _ Destination:=Worksheets("Data1").Range("G2")

where the list of suppliers is in A2:A50 of sheet "SupplierList" in the supplier workbook, and the list is copied to G2:G50 (copies the entire list into cells starting in G2) in worksheet Data1 of the combobox workbook (ThisWorkbook).

If the number of suppliers in the supplier workbook is variable, this code could be made to automatically adjust thus:

Code:
With Workbooks("Suppliers.xls").Sheets("SupplierList")
   .Range("A2",.Range("A65536").End(xlUp)).Copy _ Destination:=Worksheets("Data1").Range("G2")
End With

Keep Excelling.

Damon
 

chris115

New Member
Joined
Sep 29, 2006
Messages
9

ADVERTISEMENT

Thanks Damon, the problem I have with doing it that way is the supplier list is very long!

The workbook with the combobox is used as a form someone fills in, so don't really want to copy the whole list into this workbook.

Is there any other way to do it?

Thanks
Chris
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Chris

Instead of ListFillRange you could use AddItem to populate the combobox.

The exact code for that would depend on where the combobox is located.
 

chris115

New Member
Joined
Sep 29, 2006
Messages
9

ADVERTISEMENT

I've tried to populate the combobox using addItem, but i'm getting a "subscript out of range" error.
It's as if it can't find the suppliers file (even though its open) when it tries to activate that workbook to populate the combobox.

Is my code right?

Code:
Private Sub Workbook_Open()

Application.ScreenUpdating = False

currwb = ActiveWorkbook.Name

Workbooks.Open Filename:="O:\SUPPLIERS.xls"

Windows(currwb).Activate

'Populate Combobox
Windows("SUPPLIERS.xls").Activate
Dim x As Worksheet, i As Integer
Set x = Worksheets("SUPPLIER")
i = 1
With ComboBox1
.Clear
Do Until IsEmpty(x.Cells(i, 1))
.AddItem (x.Cells(i, 1))
i = i + 1
Loop
End With


End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Chris

Does this help?

Note the comment regarding referencing the worksheet.
Code:
Private Sub Workbook_Open()
Dim wbSupp As Workbook
Dim wbCurr As Workbook
Dim x As Worksheet
Dim I As Integer

    Set wbCurr = ActiveWorkbook
    
    Set wbSupp = Workbooks.Open(Filename:="O:\SUPPLIERS.xls")
    
    Set x = wbSupp.Worksheets("SUPPLIER")
    I = 1
    With wbCurr.Worksheets("Sheet1").ComboBox1  ' you probably need to reference the worksheet
                                                ' the combobox is on here
        .Clear
        Do Until IsEmpty(x.Cells(I, 1))
            I = I + 1
            .AddItem x.Cells(I, 1)
        Loop
        
    End With
    
    wbSupp.Close

End Sub
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top