Initializing combo boxes in worksheet (not form) — Error: object required

Question

I have two combo boxes in Sheet2 (not a UserForm), named Xchoose and Ychoose. I would like to populate them with values from cells in Sheet1 when the workbook is opened.

At this point, I am unable to even add a static string to the combo boxes. Here is my setup so far...

In ThisWorkbook, I have:

Sub Workbook_Open()
    Call Sheet2.Init_Lists
End Sub

In Sheet2 (Compare), I have:

Sub Init_Lists()
    MsgBox "Initializing..."
    Xchoose.AddItem "item 1"
    ' This subroutine will eventually add values from Sheet1.
End Sub

Sub Xchoose_Change()
    ' To do.
End Sub

Sub Ychoose_Change()
    ' To do.
End Sub

The "Initializing..." message box appears when the workbook is opened, indicating that Init_Lists works properly, but then the program breaks on the line

Xchoose.AddItem "item 1"

with the error "Object required." I have tried putting the subroutines in a separate module, but I receive the same error.

Can somebody explain how I can add items to these combo boxes? Thank you!

Form controls... but I am working on changing that at the moment. — tomocafe

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-08-16T20:18:28

Try this (TRIED AND TESTED FOR ACTIVEX COMBO)

Private Sub Workbook_Open()
    Sheet2.Xchoose.AddItem "item 1"
End Sub

OR

Sub Init_Lists()
    MsgBox "Initializing..."
    Sheet2.Xchoose.AddItem "item 1"
    ' This subroutine will eventually add values from Sheet1.
End Sub

Initializing combo boxes in worksheet (not form) — Error: object required

1 Answers