Workbook_Open and ComboBox

  • Thread starter Thread starter Brian Andrus
  • Start date Start date
B

Brian Andrus

I want to run the following code when my worksheet is open:
---------------------
Sheets("Event Setup").Activate
'Clear ListBox
For Count = 1 To ComboBox1.ListCount
ComboBox1.RemoveItem (0)
Next
ComboBox1.AddItem ("<Select Vendor>")

NumberOfVendors = Range("NumberOfVendors").Value
Count = 1
Do While (NumberOfVendors >= Count)
VendorName = Range("E" & Count).Value
MsgBox (VendorName)
ComboBox1.AddItem (VendorName)
Count = Count + 1
Loop
ComboBox1.ListIndex = 0
------------------------------

I put it in a Workbook_Open macro, but get an error:
"object required: on the ComboBox1.ListCount line.

How can I run this code when my workbook is opened?
 
Use Option Explicit by default - it identifies these problems early.
From VB menu: Tools | Options | Require Variable Declaration

As for your problem, it can't find ComboBox1 unless you give it a sheet
name.
Also tweaked your ComboBox1 clear routine. There's a Clear method of
ComboBox which does it for you.


Option Explicit

Sub test()
Dim Count As Long, NumberOfVendors As Long, VendorName As String

With Sheets("Event Setup")
.Activate

.ComboBox1.Clear 'Clear ListBox
.ComboBox1.AddItem ("<Select Vendor>")

NumberOfVendors = Range("NumberOfVendors").Value
Count = 1
Do While (NumberOfVendors >= Count)
VendorName = Range("E" & Count).Value
MsgBox (VendorName)
.ComboBox1.AddItem (VendorName)
Count = Count + 1
Loop
.ComboBox1.ListIndex = 0
End With
End Sub
 
Back
Top