Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo box with a function
Hi,
I want to use a function to populate some control form combo boxes on a worksheet. However, I'm not sure how to pass the combo box name to the function or even if this is the right approach. I suspect the line where I define "MyCombo" is wrong but what should I have instead? My code reads: Sub xx() MyCombo = Sheets("Entry sheet").ComboBox1 ' Add unique items to dropdown box Call FillCombo(MainCategory, MyCombo) ' etc End Sub And then my function: Function FillCombo(Category As Collection, MyCombo As ComboBox) Dim Item With Sheets("Entry sheet").ComboBox1 .Clear .AddItem "<All" For Each Item In Category .AddItem Item Next Item .Text = "<All" End With End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo box with a function
It looks OK. are you having problems? I added a few enrichments
Sub xx() Dim MyCombo as MSForms.Combobox MyCombo = Sheets("Entry sheet").ComboBox1 ' Add unique items to dropdown box Call FillCombo(MainCategory, MyCombo) ' etc End Sub And then my function: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Dim Item With MyCombo .Clear .AddItem "<All" For Each Item In Category .AddItem Item Next Item .Text = "<All" End With End Sub since you aren't returning a value, no need to make this a function I assume the items in your category are strings. -- Regards, Tom Ogilvy "Derek Gadd" wrote in message om... Hi, I want to use a function to populate some control form combo boxes on a worksheet. However, I'm not sure how to pass the combo box name to the function or even if this is the right approach. I suspect the line where I define "MyCombo" is wrong but what should I have instead? My code reads: Sub xx() MyCombo = Sheets("Entry sheet").ComboBox1 ' Add unique items to dropdown box Call FillCombo(MainCategory, MyCombo) ' etc End Sub And then my function: Function FillCombo(Category As Collection, MyCombo As ComboBox) Dim Item With Sheets("Entry sheet").ComboBox1 .Clear .AddItem "<All" For Each Item In Category .AddItem Item Next Item .Text = "<All" End With End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo box with a function
Thanks Tom but the problem still occurs, namely the message:
Object variable or With block variable not set (Error 91) It fails at the line: MyCombo = Entry.ComboBox1 Prior to that line I have declared the variables etc with: Dim MyCombo As MSForms.ComboBox Dim Entry As Object Set Entry = Sheets("Entry sheet") Using MyCombo=Sheets("Entry sheet").ComboBox1 does not work either. The combo box is on a sheet called "Entry sheet" and is from the control toolbox. Any ideas? Thanks, Derek "Tom Ogilvy" wrote in message ... It looks OK. are you having problems? I added a few enrichments Sub xx() Dim MyCombo as MSForms.Combobox MyCombo = Sheets("Entry sheet").ComboBox1 ' Add unique items to dropdown box Call FillCombo(MainCategory, MyCombo) ' etc End Sub And then my function: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Dim Item With MyCombo .Clear .AddItem "<All" For Each Item In Category .AddItem Item Next Item .Text = "<All" End With End Sub since you aren't returning a value, no need to make this a function I assume the items in your category are strings. -- Regards, Tom Ogilvy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo box with a function
Sub xx()
Dim MyCombo as MSForms.Combobox Dim Entry As Worksheet set Entry = Worksheets("Entry Sheet") Set MyCombo = Entry.ComboBox1 ' <= should be Set ' Add unique items to dropdown box Call FillCombo(MainCategory, MyCombo) ' etc End Sub And then my function: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Dim Item With MyCombo .Clear .AddItem "<All" For Each Item In Category .AddItem Item Next Item .Text = "<All" End With End Sub -- Regards, Tom Ogilvy "Derek Gadd" wrote in message om... Thanks Tom but the problem still occurs, namely the message: Object variable or With block variable not set (Error 91) It fails at the line: MyCombo = Entry.ComboBox1 Prior to that line I have declared the variables etc with: Dim MyCombo As MSForms.ComboBox Dim Entry As Object Set Entry = Sheets("Entry sheet") Using MyCombo=Sheets("Entry sheet").ComboBox1 does not work either. The combo box is on a sheet called "Entry sheet" and is from the control toolbox. Any ideas? Thanks, Derek "Tom Ogilvy" wrote in message ... It looks OK. are you having problems? I added a few enrichments Sub xx() Dim MyCombo as MSForms.Combobox MyCombo = Sheets("Entry sheet").ComboBox1 ' Add unique items to dropdown box Call FillCombo(MainCategory, MyCombo) ' etc End Sub And then my function: Sub FillCombo(Category As Collection, MyCombo As MSForms.ComboBox) Dim Item With MyCombo .Clear .AddItem "<All" For Each Item In Category .AddItem Item Next Item .Text = "<All" End With End Sub since you aren't returning a value, no need to make this a function I assume the items in your category are strings. -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo box with a function
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo box with a function
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating a cell based on a specific date/combo | Excel Worksheet Functions | |||
vba function ( populating rows) | Excel Discussion (Misc queries) | |||
Populating a list/combo box with an Access column. | Excel Discussion (Misc queries) | |||
Populating a lis/combo box in excel with access data | Excel Programming | |||
Populating Combo Boxes | Excel Programming |