Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing combobox | Excel Programming | |||
Combobox value to be set as Target for referencing from. How ? | Excel Programming | |||
Referencing Combobox with other cells in a different worksheet?? | Excel Programming | |||
referencing ComboBox | Excel Programming | |||
referencing ComboBox | Excel Programming |