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

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

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

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

Populating combo box with a function
 

Hi,
Set MyCombo = Entry.ComboBox1
as MyCombo is an object (it has properties and methods).
regards
Paul

(Derek Gadd) wrote in message . com...
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

Populating combo box with a function
 

Thanks Paul, Tom. That works great!

(Paul Robinson) wrote in message . com...
Hi,
Set MyCombo = Entry.ComboBox1
as MyCombo is an object (it has properties and methods).
regards
Paul