Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open and ComboBox
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_Open and ComboBox
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 -- Rob van Gelder - http://www.vangelder.co.nz/excel "Brian Andrus" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Workbook_Open () | Excel Discussion (Misc queries) | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming | |||
Help with Workbook_Open | Excel Programming | |||
Workbook_Open event and combobox state storage question | Excel Programming |