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
|