Referencing a ComboBox in VBA
 

Hi,

I am using Excel 2007. I want to run some code in my Workbook_Open() Sub
that fills a combo box on one of my Worksheets (I will call it myCombo on
mySheet). I tried the following code and it did not work:

myCombo.Clear
myCombo.AddItem "Item One"
myCombo.AddItem "Item Two"
myCombo.AddItem "Item Three"

I assume that the problem is that within the Workbook_Open() Sub, the
reference to myCombo is not valid. Can anyone tell me if/how I can get a
valid reference to myCombo (on mySheet)?

Thanks in advance,
Paul Kraemer



--
Paul Kraemer

Referencing a ComboBox in VBA
 

One way:

Option Explicit
Private Sub Workbook_Open()
Dim wks As Worksheet

Set wks = Me.Worksheets("mySheet")

With wks.OLEObjects("myCombo").Object
.Clear
.AddItem "item one"
.AddItem "item 2"
.AddItem "3"
End With
End Sub



Paul Kraemer wrote:

Hi,

I am using Excel 2007. I want to run some code in my Workbook_Open() Sub
that fills a combo box on one of my Worksheets (I will call it myCombo on
mySheet). I tried the following code and it did not work:

myCombo.Clear
myCombo.AddItem "Item One"
myCombo.AddItem "Item Two"
myCombo.AddItem "Item Three"

I assume that the problem is that within the Workbook_Open() Sub, the
reference to myCombo is not valid. Can anyone tell me if/how I can get a
valid reference to myCombo (on mySheet)?

Thanks in advance,
Paul Kraemer

--
Paul Kraemer

--

Dave Peterson

Referencing a ComboBox in VBA
 

You need to reference the worksheet that the combobox is on:

Worksheets(1).myCombo.Clear
worksheets(1).myCoimbo.AddItem "Item One"
etc.



"Paul Kraemer" wrote in message
...
Hi,

I am using Excel 2007. I want to run some code in my Workbook_Open() Sub
that fills a combo box on one of my Worksheets (I will call it myCombo on
mySheet). I tried the following code and it did not work:

myCombo.Clear
myCombo.AddItem "Item One"
myCombo.AddItem "Item Two"
myCombo.AddItem "Item Three"

I assume that the problem is that within the Workbook_Open() Sub, the
reference to myCombo is not valid. Can anyone tell me if/how I can get a
valid reference to myCombo (on mySheet)?

Thanks in advance,
Paul Kraemer



--
Paul Kraemer

Referencing a ComboBox in VBA
 

Give this a try. This should work if it is an ActiveX dropdown box. Hope
this helps! If so, let me know, click "YES" below.

Private Sub Workbook_Open()

With Sheets("Sheet1").myCombo
.Clear
.AddItem "Item One"
.AddItem "Item Two"
.AddItem "Item Three"
End With

End Sub
--
Cheers,
Ryan


"Paul Kraemer" wrote:

Hi,

I am using Excel 2007. I want to run some code in my Workbook_Open() Sub
that fills a combo box on one of my Worksheets (I will call it myCombo on
mySheet). I tried the following code and it did not work:

myCombo.Clear
myCombo.AddItem "Item One"
myCombo.AddItem "Item Two"
myCombo.AddItem "Item Three"

I assume that the problem is that within the Workbook_Open() Sub, the
reference to myCombo is not valid. Can anyone tell me if/how I can get a
valid reference to myCombo (on mySheet)?

Thanks in advance,
Paul Kraemer



--
Paul Kraemer