PDA

View Full Version : List box in cell?



mleary2001
02-07-2006, 04:23 PM
Can I dynamically add a ListBox to a cell in a spreadsheet?

Thanks in advance,
Mike:banghead:

mdmackillop
02-07-2006, 04:41 PM
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

mleary2001
02-07-2006, 05:02 PM
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
02-08-2006, 12:36 AM
What version of Excel do you have. This example is from the 2003 help files

mleary2001
02-08-2006, 11:12 AM
I have

Microsoft Excel X for Mac Service Release 1.

Thanks again for all your help.

Mike

mdmackillop
02-08-2006, 11:27 AM
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
02-10-2006, 07:18 AM
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
04-23-2007, 09:18 AM
Hi, Ive tried to change the control ListFillRange of a combobox that shows me the plans. I tried these so far:

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

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

Set lb = .Shapes("cbxCenterPlans")
lb.ControlFormat.ListFillRange = "Controls!" & Sheets("Controls").Range("A3").CurrentRegion.Address


with the same results, thanks

shades
04-23-2007, 05:01 PM
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
06-05-2007, 06:21 PM
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.