Access control on a worksheet - help!

[MonkeyMan]

Hi,
can anybody help me? I am trying to access a listbox control on a
worksheet and populate it with an array. For example:

Sub MySub()
Dim mySheet As Worksheet
Dim tempArray As Variant

' populate the tempArray ........

Set mySheet = Sheets("Test sheet")
mySheet.ListBox1.List() = tempArray 'A variant array
End Sub

The error message is: Compile error, method or data member not found

However this works just fine: Sheets("Test sheet").ListBox1.List() =
tempArray

I would like to use the variable mySheet instead (better programming
practice I assume!)

Any ideas? Thanks in advance to all

 

[Dick Kusleika]

MonkeyMan

I don't know why that is, but this works:

mySheet.OLEObjects("ListBox1").List()=...

 

[onedaywhen]

Or change the sheet declaration to Object i.e.

Dim mySheet As Object

--

 

[Dick Kusleika]

ODW

Good one. That makes it a little clearer. Worksheet is a class that
represents all (each) worksheet. Since every worksheet isn't likely to have
a ListBox1, then the general Worksheet class won't have that data member.
But if you declared like

Dim mySheet as Sheet1

it would also work because you're declaring the variable as that specific
class. I'm surprised that Object resolves to the specific class, but that's
not entirely without merit. And in either case, TypeName(mysheet) is
Worksheet.

 

[MonkeyMan]

Cheers Guys, great help!