| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unable to view color fill changes unless I print preview | Excel Worksheet Functions | |||
| Is there an easier way to fill this listbox | Excel Programming | |||
| R1C1 seems to effect listbox fill in VBA | Excel Programming | |||
| Fill a ListBox with items using VBA | Excel Programming | |||
| For..next.. help to fill listbox | Excel Programming | |||
Linear Mode
