ComboBox fill problem

[chris115]

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

 

[Damon Ostrander]

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]

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]

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:

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:

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

Keep Excelling.

Damon

 

[chris115]

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]

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]

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?

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]

Chris

Does this help?

Note the comment regarding referencing the worksheet.

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

 

[chris115]

Cheers, massive help.