Userform Combobox

[Excel User]

Hi,

I have a userform that contains a combobox, I thought I could add a range to
the additem i.e.

With ComboBox1
.AddItem MyList
End With

where MyList is a range that is present on a worksheet.

Thanks

 

[Dave Peterson]

me.combobox1.list = myRng.value
(where myRng is a range)

or
me.combobox1.list = myList
(where myList is an array of values)

 

[JLGWhiz]

If you used AddItem or List to initially load your combobox, you should be
able to add additional items using the AddItem method. However, if you
initially loaded your combobox using RowSource or ListFillRange, then you
cannot use AddItem to amend the current list.

 

[Excel User]

Dave,

I'm alittle lost, I've tried :

Dim myRng As Range
myRng = myRange.Value

'where myRange is a named range on a sheet named 'item list'

ComboBox.List = myRng

but I get an error message.


Run-time error '424'
Object required

Not sure what I am doing wrong?

 

[JLGWhiz]

I forgot to add that I assumed you are putting the code into the UserForm
code module, maybe in the initialize event. It won't work from the standard
code module1 unless the UserForm is open while the code runs and that is
another whole scenario.

Dave,

I'm alittle lost, I've tried :

Dim myRng As Range
myRng = myRange.Value

'where myRange is a named range on a sheet named 'item list'

ComboBox.List = myRng

but I get an error message.


Run-time error '424'
Object required

Not sure what I am doing wrong?

 

[JLGWhiz]

You can write it a couple of ways:

Me.ComboBox1.List = myRng

or:

UserForm1.ComboBox1.List = myRng

the user form has to be included to tell the compiler where to look for the
combobox. Also you have to tell it which combobox you are referring to.
Tthe control name must be specific so VBA knows which one to look at. As you
add and delete controls, VBA will automatically name them sequentially, but
you can change the names to something more recognizable by you that maybe
tells you what the control does. You can then use that name in your code
instead of combobox1, combobox2, etc.

Dave,

I'm alittle lost, I've tried :

Dim myRng As Range
myRng = myRange.Value

'where myRange is a named range on a sheet named 'item list'

ComboBox.List = myRng

but I get an error message.


Run-time error '424'
Object required

Not sure what I am doing wrong?

 

[Dave Peterson]

If myRange is a named range, then try:

me.combobox1.list = worksheets("item list").range("myRange").value

or

dim myRng as range
set myrng = worksheets("item list").range("myRange")
me.combobox1.list = myrng.value

or

dim myList as variant
mylist = worksheets("item list").range("myRange").value
my.combobox1.list = mylist

 

[Excel User]

Thanks Dave and JLGWhiz,

I've got it know!

If myRange is a named range, then try:

me.combobox1.list = worksheets("item list").range("myRange").value

or

dim myRng as range
set myrng = worksheets("item list").range("myRange")
me.combobox1.list = myrng.value

or

dim myList as variant
mylist = worksheets("item list").range("myRange").value
my.combobox1.list = mylist