Populate Combo Box

[Scott]

I have a sheet named "Data" that I added a combo box to. I'm trying to use
the Worksheet_Activate() event to populate the combo box. After creating the
combo box, I left the "Input Range" blank and entered the below code in the
sheet's module. When I test the sheet by selecting it, I get an "object
required" error.

Just to test, I added an input range that contains blank cells, but left my
code as is. After testing it, the error went away, but my code doesn't fire.
I even put a test message box in to confirm that the Worksheet_Activate()
event below was not firing.

How can I populate a combo box that is on a sheet (not a form)?

CODE:

Private Sub Worksheet_Activate()
dropdown1.Clear
dropdown1.AddItem "Shift 1"
dropdown1.AddItem "Shift 2"
dropdown1.Text = dropdown1.List(0)

End Sub

 

[FSt1]

hi
i am guessing that it does fire but doesn't know what to do so do nothing.
change your code to this....modify to suit.....
Private Sub Worksheet_Activate()
Sheet1.dropdown1.Clear
Sheet1.dropdown1.AddItem "Shift 1"
Sheet1.dropdown1.AddItem "Shift 2"
Sheet1.dropdown1.Text = dropdown1.List(0)
End Sub

works in xp

Regards
FSt1

 

[Scott]

I hate to give up on a coding task, but I just can't get it working. Perhaps
it's because I'm on Vista. Its hard for me to believe that the OS would have
an effect on how Excel 2003 VBA works.

Let me know if you think of any other test that may help. Is there a way
with vba to iterate through a sheet and display the names of every combo box
that in on a sheet?

 

[Dave Peterson]

You used Dropdown and Input Range--did you mean that the object came from the
Forms toolbar?

If yes:

Option Explicit
Private Sub Worksheet_Activate()
Dim myDD As DropDown
Set myDD = Me.DropDowns("drop down 1")

With myDD
.RemoveAllItems
.AddItem "Shift 1"
.AddItem "Shift 2"
.ListIndex = 0 'nothing showing, 1 for first, 2 for second
End With

End Sub

If you used the combobox from the Control toolbox toolbar:

Option Explicit
Private Sub Worksheet_Activate()

With Me.ComboBox1
.Clear
.AddItem "Shift 1"
.AddItem "Shift 2"
.ListIndex = -1 'nothing, 0 for the first, 1 for the second
End With

End Sub

(Yep, the .listindex stuff is different!)

 

[Scott]

Yes, I created the combo from using the forms toolbar. Was I right?

 

[Dave Peterson]

It's not a matter of right or wrong. It's a matter of what one you wanted. (I
see nothing wrong with the Dropdowns from the Forms toolbar.)

But was I right with the first suggestion? <vbg>

 

[Scott]

thank you.

You used Dropdown and Input Range--did you mean that the object came from
the
Forms toolbar?

If yes:

Option Explicit
Private Sub Worksheet_Activate()
Dim myDD As DropDown
Set myDD = Me.DropDowns("drop down 1")

With myDD
.RemoveAllItems
.AddItem "Shift 1"
.AddItem "Shift 2"
.ListIndex = 0 'nothing showing, 1 for first, 2 for second
End With

End Sub

If you used the combobox from the Control toolbox toolbar:

Option Explicit
Private Sub Worksheet_Activate()

With Me.ComboBox1
.Clear
.AddItem "Shift 1"
.AddItem "Shift 2"
.ListIndex = -1 'nothing, 0 for the first, 1 for the second
End With

End Sub

(Yep, the .listindex stuff is different!)