|
Unable to fill Listbox thru VBA
Hi, I want to fill a listbox thru VBA not connecting a range to it. Every thing I tru (such as AddItem method) result i the error msg "The Object dosent support his method or property" Even if I copy the Excel Help Example, why is that? Kind regards! -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 |
Unable to fill Listbox thru VBA
Hi Rash,
Try posting your code which fails. --- Regards, Norman "rash" wrote in message ... Hi, I want to fill a listbox thru VBA not connecting a range to it. Every thing I tru (such as AddItem method) result i the error msg "The Object dosent support his method or property" Even if I copy the Excel Help Example, why is that? Kind regards! -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 |
Unable to fill Listbox thru VBA
Ok here it goes. AddItem dosent work, nor dose list. Kind regards Code ------------------- Dim myDoc As Worksheet Dim list As Variant list = ThisWorkbook.Sheets("Sheet1").Range("A2:A4").Value Set myDoc = ThisWorkbook.Sheets("Sheet1") myDoc.Shapes("ListBox1").list = list ' populate the listbox myDoc.Shapes("ListBox1").ControlFormat.AddItem ("Whatever" ------------------- Norman Jones Wrote: Hi Rash, Try posting your code which fails. --- Regards, Norman "rash" wrote i message ... Hi, I want to fill a listbox thru VBA not connecting a range to it. Every thing I tru (such as AddItem method) result i the error ms "The Object dosent support his method or property" Even if I copy th Excel Help Example, why is that? Kind regards! -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread http://www.excelforum.com/showthread...hreadid=494825 -- ras ----------------------------------------------------------------------- rash's Profile: http://www.excelforum.com/member.php...fo&userid=2976 View this thread: http://www.excelforum.com/showthread.php?threadid=49482 |
Unable to fill Listbox thru VBA
Hi Rash,
AddItem dosent work, nor dose list. Try: '============== Sub Tester01() Dim OleObj As OLEObject Dim SH As Worksheet Dim rng As Range Dim rCell As Range Set SH = ActiveSheet Set rng = SH.Range("A1:A20") Set OleObj = SH.OLEObjects("ListBox1") With OleObj .ListFillRange = "" .Object.Clear For Each rCell In rng.Cells .Object.AddItem rCell.Value Next rCell End With End Sub '<<============== Or: '============== Sub Tester02() Dim OleObj As OLEObject Set OleObj = ActiveSheet.OLEObjects("ListBox1") OleObj.ListFillRange = "A1:A24" End Sub '<<============== --- Regards, Norman "rash" wrote in message ... Ok here it goes. AddItem dosent work, nor dose list. Kind regards Code: -------------------- Dim myDoc As Worksheet Dim list As Variant list = ThisWorkbook.Sheets("Sheet1").Range("A2:A4").Value Set myDoc = ThisWorkbook.Sheets("Sheet1") myDoc.Shapes("ListBox1").list = list ' populate the listbox myDoc.Shapes("ListBox1").ControlFormat.AddItem ("Whatever") -------------------- Norman Jones Wrote: Hi Rash, Try posting your code which fails. --- Regards, Norman "rash" wrote in message ... Hi, I want to fill a listbox thru VBA not connecting a range to it. Every thing I tru (such as AddItem method) result i the error msg "The Object dosent support his method or property" Even if I copy the Excel Help Example, why is that? Kind regards! -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 |
Unable to fill Listbox thru VBA
Sub abc()
v = Array(1, 2, 3, 4, 5) ActiveSheet.OLEObjects("ListBox1").Object.List = v End Sub Sub abc1() For i = 1 To 7 ActiveSheet.OLEObjects("Listbox2").Object.AddItem i Next End Sub so Dim myDoc As Worksheet Dim list As Variant list = ThisWorkbook.Sheets("Sheet1").Range("A2:A4").Value Set myDoc = ThisWorkbook.Sheets("Sheet1") myDoc.OleObjects("ListBox1").Object.list = list ' populate the listbox ' or myDoc.OleObjects("ListBox1").Object.AddItem "Whatever" -- Regards, Tom Ogilvy "rash" wrote in message ... Ok here it goes. AddItem dosent work, nor dose list. Kind regards Code: -------------------- Dim myDoc As Worksheet Dim list As Variant list = ThisWorkbook.Sheets("Sheet1").Range("A2:A4").Value Set myDoc = ThisWorkbook.Sheets("Sheet1") myDoc.Shapes("ListBox1").list = list ' populate the listbox myDoc.Shapes("ListBox1").ControlFormat.AddItem ("Whatever") -------------------- Norman Jones Wrote: Hi Rash, Try posting your code which fails. --- Regards, Norman "rash" wrote in message ... Hi, I want to fill a listbox thru VBA not connecting a range to it. Every thing I tru (such as AddItem method) result i the error msg "The Object dosent support his method or property" Even if I copy the Excel Help Example, why is that? Kind regards! -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 |
Unable to fill Listbox thru VBA
Works like a charm! Why dose it work with OLEObject? Kind regard -- ras ----------------------------------------------------------------------- rash's Profile: http://www.excelforum.com/member.php...fo&userid=2976 View this thread: http://www.excelforum.com/showthread.php?threadid=49482 |
Unable to fill Listbox thru VBA
Hi Rash,
Why dose it work with OLEObject? An OleObject and, more particularly, a listbox, has methods and properties whih differ from those of a shape object. BTW, check Tom's response for some instructive code. --- Regards, Norman "rash" wrote in message ... Works like a charm! Why dose it work with OLEObject? Kind regards -- rash ------------------------------------------------------------------------ rash's Profile: http://www.excelforum.com/member.php...o&userid=29765 View this thread: http://www.excelforum.com/showthread...hreadid=494825 |
| All times are GMT +1. The time now is 06:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com