VBA Combobox in excel sheet

Hi, I'm new to this forum and new to VBA, I have a small question that I need a little help with.

I'm using excel 2003.

basically what I'm trying to do is create a combobox on sheet 1 of my workbook.

the code I've used is...


Private Sub ComboBox1_DropButtonClick()
With ComboBox1
ComboBox1.AddItem "aaa"
ComboBox1.AddItem "bbb"
ComboBox1.AddItem "ccc"
End With
End Sub

this does work, but each time I click on the combo box it repeats the list over and over again for each time I clikc on the list.

hope this is making sense.

thanks for any help in advance.

regards
Sukh

Hi, sorry I've been away for a while, I will try this.

thanks for your help

regards
Sukh

Sorry if I'm being a little slow

my code now looks like

Private Sub ComboBox1_DropButtonClick()

With ComboBox1

ComboBox1.Clear

ComboBox1.AddItem "aaa"
ComboBox1.AddItem "bbb"
ComboBox1.AddItem "ccc"

End With

End Sub

but now each time I select a entry it doesnt display in the combo box.

any help is much apprectaied

Ok, I've done this now,

in the sheet 1 code window I have the following code

Private Sub Worksheet_Activate()

Sheets("sheet1").Select


With ComboBox1

ComboBox1.Clear

ComboBox1.AddItem "aaa"
ComboBox1.AddItem "bbb"
ComboBox1.AddItem "ccc"

End With

End Sub

when I open the workbook, it doesnt activate the combo box, I have to click on sheet2 tab in excel and then sheet 1 again and then the combo box works.

I tried activating the worksheet in workbook open like...

Private Sub Workbook_Open()

Sheets("sheet1").Select

End Sub

but this doesnt activate the combo box either, sorry if im being slow :s

Apologies I hope this is the correct way of posting

The code for sheet 1.

Please Login or Register  to view this content.

The code for ThisWorkBook

Please Login or Register  to view this content.

but sheet 1 doesnt seem to automatically activate, any suggestions?

thanks

ahh man, thankyou it worked.

much appreciated.

regards
Sukh