List box in cell?

[mleary2001]

Can I dynamically add a ListBox to a cell in a spreadsheet?

Thanks in advance,
Mike

[mdmackillop]

[VBA]Sub AddList()
[A1] = "test1"
[A2] = "test2"
[A3] = "test3"
[A4] = "test4"
[A5] = "test5"
With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, [G3].Left, [G3].Top, [G3].Width, [G3].Height)
lb.ControlFormat.ListFillRange = "A1:A5"
End With
End Sub[/VBA]

[mleary2001]

Thanks for the incredibly quick response! I had to modify it like below, but I get a compiler error: Method or data member not found - with the ControlFormat highlighted.

Public Sub add_list()
Dim list_box As listbox
[A1] = "test1"
[A2] = "test2"
[A3] = "test3"
[A4] = "test4"
[A5] = "test5"
With Worksheets(1)
Set list_box = .Shapes.AddFormControl(xlListBox, [G3].Left, [G3].Top, [G3].Width, [G3].Height)
list_box.ControlFormat.ListFillRange = "A1:A5"
End With
End Sub

[mdmackillop]

What version of Excel do you have. This example is from the 2003 help files

[mleary2001]

I have

Microsoft Excel X for Mac Service Release 1.

Thanks again for all your help.

Mike

[mdmackillop]

Apologies,
I never noticed that this was in the Mac forum, and nobody asked me for my passport when I entered this foreign country. Maybe one of the locals can assist with the last bit.
Regards
MD

[shades]

Howdy, MD. That's okay, we welcome any strangers in these here parts.

If this is an ActiveX Control then that is not supported in Mac VBA (based on VB5). You may have to switch to using Forms Control, but not sure how that would work. I'm not at my Mac to check/test.

[makako]

Hi, Ive tried to change the control ListFillRange of a combobox that shows me the plans. I tried these so far:

[VBA]Private Sub Workbook_Open()
Dim lvRango As Range
Set lvRango = Sheets("Controls").Range("A3")
Range(lvRango, lvRango.End(xlDown)).Delete
Set lvRango = Sheets("Plans").Columns("A:A").SpecialCells(xlCellTypeConstants)
lvRango.Copy Sheets("Controls").Range("A3")
'Sheets("Planes").Controls("cbxCenterPlans").ListFillRange = Sheets("Controls").Range("A3").CurrentRegion.Address
Set lb = Sheets("Planes").Controls("cbxCenterPlans")
lb.ListFillRange = "Controls!" & Sheets("Controls").Range("A3").CurrentRegion.Address
End Sub[/VBA]

After having all the elements in the correct range i try to change the property of the combo box but it keeps showing me errors. I tried
[VBA]
Set lb = .Shapes("cbxCenterPlans")
lb.ControlFormat.ListFillRange = "Controls!" & Sheets("Controls").Range("A3").CurrentRegion.Address
[/VBA]

with the same results, thanks

[shades]

Howdy. After thinking about this, would it not work as well to use Validation in the cell instead of a ListBox? And you could use a dynamic named range so that it would automatically expand.

[mikerickson]

Hello, welcome to the world of Forms controls. It frustrates me that the ActiveX controls are not avaliable on my Mac.
This is a way to make a list box.

Dim LBox As ListBox
Set LBox = Sheets(1).ListBoxes.Add(100, 100, 100, 100)
LBox.ListFillRange = "$H$1:$H$12"

But LBox is in Sheets(1).Shapes, not .OLEObects
I think you will find this syntax useful

Sheets(1).Shapes(1).ControlFormat.AddItem "ssssss"

The Object Browser is your friend.