Can I dynamically add a ListBox to a cell in a spreadsheet?
Thanks in advance,
Mike
Can I dynamically add a ListBox to a cell in a spreadsheet?
Thanks in advance,
Mike
[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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
What version of Excel do you have. This example is from the 2003 help files
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I have
Microsoft Excel X for Mac Service Release 1.
Thanks again for all your help.
Mike
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Software: LibreOffice 3.3 on Mac OS X 10.6.5
(retired Excel 2003 user, 3.28.2008 )
Humanware: Older than dirt
--------------------
old, slow, and confused
but at least I'm inconsistent!
Rich
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
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.
Software: LibreOffice 3.3 on Mac OS X 10.6.5
(retired Excel 2003 user, 3.28.2008 )
Humanware: Older than dirt
--------------------
old, slow, and confused
but at least I'm inconsistent!
Rich
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.
But LBox is in Sheets(1).Shapes, not .OLEObectsDim LBox As ListBox Set LBox = Sheets(1).ListBoxes.Add(100, 100, 100, 100) LBox.ListFillRange = "$H$1:$H$12"
I think you will find this syntax useful
The Object Browser is your friend.Sheets(1).Shapes(1).ControlFormat.AddItem "ssssss"
Last edited by mikerickson; 06-05-2007 at 06:37 PM.