Listboxes Without User Form

[Vipul]

Hello people,

I have one combobox on my sheet and I am trying to put a listbox on the same
sheet.
so when i click on Add To List button it should add the selected value from
combobox to the listbox.
but i m not able to insert a listbox.
can anyone please help me with this.


This is the code i m using to insert a listbox on my sheet.
and i m getting Type Mismatch error

Sub TEST()
Dim oOLE As OLEObject
Dim lstCustomers As ListBox

Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1",
Left:=537, Top:=89, Width:=120, Height:=15)

Set lstCustomers = oOLE.Object
End Sub

hope to hear something on this.
Vipul Agheda

 

[Bob Phillips]

Why are you trying to add it in code, why not just add it in design?

 

[Dave Peterson]

Use:
Dim lstCustomers As msforms.ListBox

When you used:
Dim lstCustomers As ListBox

You were using the listbox from the Forms toolbar.

 

[Vipul]

hello bob,
thanks for your reply and question

actually i tried to add the listbox control in design mode, just straight
from the Forms toolbox to my sheet
but if i add it like that then i m not getting all those controls in the
coding.
i mean for example if i draw a listbox on my sheet, and name it as ListBox1,
then i am not getting that control in code.

thats why i m trying to add it using code.
if you know how can i use drawn controls in coding please please let me
know. thats what i wanted basically.

thanks again.
cheers mate

 

[Vipul]

hey Dave
thanks a lot mate
it worked for me.
u know i m trying to get this for last 3 days.
and finally i got it from u
many thanks bro
cheers mate

 

[Vipul]

hi again dave,

now i got another problem
when i tried to add items in the list box i created, i m not able to
retrieve the added items but not able to see them.

here is the code:

Sub TEST1()
Dim oOLE As OLEObject
Dim lstCustomers As MSFORMS.ListBox
Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1",
Left:=537, Top:=189, Width:=120, Height:=150)
Set lstCustomers = oOLE.Object
lstCustomers.AddItem "A"
lstCustomers.AddItem "b"
lstCustomers.AddItem "c"
lstCustomers.AddItem "d"
MsgBox lstCustomers.List(2)
End Sub

 

[Dave Peterson]

I don't know why that happens.

Maybe you could use Bob's idea and place the listbox where you want it. Just
hide it or unhide it in your code.