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!
Hi Rash,
Try posting your code which fails.
---
Regards,
Norman
"rash" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
Ok here it goes.
AddItem dosent work, nor dose list.
Kind regards
Please Login or Register to view this content.Originally Posted by Norman Jones
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > 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
>
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > >
> > > 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
>
Works like a charm!
Why dose it work with OLEObject?
Kind regards
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks