Combobox Question

[TonySondergeld]

Hi all, I have a combobox (active x) on a sheet in excel 2007. What I want it to do is fill it with the Months (Auto, January to December) and also have it run (fill) when the workbook opens. The Auto in the combobox is used to run the reports at the current month and then you can select a month so to be able to lookup data when ever you want.
So I think to be able to run it a startup of the workbook it has to go into the module is this right as I have used this code before:
Sub Fill_CM_Months()
With Sheets("Dashboard")
.OLEObjects("CM_Months").ListFillRange = "Control!I3:I15"
End With
End Sub.

But it wont work in this instant.
Thanks for any help.

 

[dscg]

Try placing this in the ThisWorkbook module:

Private Sub Workbook_Open()
With Sheets("Dashboard")
.OLEObjects("CM_Months").ListFillRange = "Control!I3:I15"
End With
End Sub

 

[TonySondergeld]

I Know it will work in either module or workbook however I must use the Additem and not the ListFillRange.
Know do I do this Sub Workbook_Open option

 

[dscg]

Something like this perhaps?

Private Sub Workbook_Open()
Dim FirstRow As Integer
Dim LastRow As Integer
FirstRow = 3
LastRow = 15
For i = FirstRow To LastRow
Sheets("Dashboard").ComboBox1.AddItem Sheets("Control").Cells(i, 9)
Next i
End Sub

 

[TonySondergeld]

I my looking for something more like this; however this has a problem
Private Sub Workbook_Open()
Dim FirstRow As Integer
Dim LastRow As Integer
Dim i As Integer
FirstRow = 3
LastRow = 15
For i = FirstRow To LastRow
Sheets("Dashboard").CB_MonthSelect.AddItem "Auto"
.AddItem "January"
.AddItem "Febuary"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
Next i
End Sub

 

[dscg]

To hard code each item, you can use this and it won't be tied to a range in the workbook any more:

Private Sub Workbook_Open()
With Sheets("Dashboard").CB_MonthSelect
.AddItem "January"
.AddItem "Febuary"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
End Sub

 

[TonySondergeld]

Thanks for the help it work great