VBA in Excel... Combo Box issue

[merlin63]

Greetings,

I've been trying for awhile to have a options appear in a ComboBox in Microsoft Excel by using the following code:

Private Sub Document_Open()

ComboBox1.Clear
ComboBox1.AddItem "Jan"
ComboBox1.AddItem "Feb"
ComboBox1.AddItem "Mar"
ComboBox1.AddItem "April"
ComboBox1.AddItem "May"
ComboBox1.AddItem "June"
ComboBox1.AddItem "July"
ComboBox1.AddItem "August"
ComboBox1.AddItem "September"
ComboBox1.AddItem "October"
ComboBox1.AddItem "November"
ComboBox1.AddItem "December"

End Sub

This works in Microsoft Word but not in Excel. When opening the document, "Jan" will appear in the Combo Box but nothing else will appear after clicking on the down arrow. Could anyone tell me what I am doing wrong?

Also, I would ultimately like to have this option pop up asking to pick a month when the worksheet first loads (Prompt the user for the month and then have it fetch data). Is this possible?

Thanks!
Dan

[seismicweasel]

the code works for me *if* you change Document_Open() to:

Worksheet_Activate()

Worksheet_Activate() is triggered when you select any given sheet in the spreadsheet. If you want to use a more global trigger Workbook_Activate() will trigger when you first open the spreadsheet

[merlin63]

I have tried both Workbook_Activate, Worksheet_Activate, Workbook_Open and Worksheet open. For some reason, nothing is coming up. I don't know if this matters, but when I open the worksheet, the only option that appears is "Jan". When clicking on the down arrow to display the rest of the options in the combo box, nothing appears. I am using Excel 2002.

[seismicweasel]

hmmm, are you writing the code within a module or are you writing the code on the specific sheet?

you have to place Worksheet_Activate() on the code of the actual sheet you want the code to effect rather than within an modules you have created.

in the project explorer select the sheet that you want Worksheet_Activate() to work on and enter the code there.

that should work, probably

[merlin63]

I am writing the code on a specific sheet (Sheet 1) and not for a module. As a test, I tried putting the code in "ThisWorkbook" under all the sheet selections in project explorer but this did not help either.

[BillBoeBaggins]

This worked for me...

Private Sub Worksheet_Activate()
    ComboBox1.Clear
    ComboBox1.AddItem "Jan"
    ComboBox1.AddItem "Feb"
    ComboBox1.AddItem "Mar"
    ComboBox1.AddItem "April"
    ComboBox1.AddItem "May"
    ComboBox1.AddItem "June"
    ComboBox1.AddItem "July"
    ComboBox1.AddItem "August"
    ComboBox1.AddItem "September"
    ComboBox1.AddItem "October"
    ComboBox1.AddItem "November"
    ComboBox1.AddItem "December"
 
End Sub

Did you make sure to take the sheet out of design mode?

[merlin63]

Still doesn't work. I tried creating a new workbook also, but that did not help. Every time I test it, I am sure to take it out of design mode.

[andreys]

try this, you may need change sheet name

Private Sub Worksheet_Activate()
    Sheet1.ComboBox1.Clear
    For i = 1 To 12
        Sheet1.ComboBox1.AddItem MonthName(i)
    Next
End Sub