Access control on a worksheet - help!
 

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 :-)

Access control on a worksheet - help!
 

MonkeyMan

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

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"MonkeyMan" wrote in message
om...
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 :-)

Access control on a worksheet - help!
 

Or change the sheet declaration to Object i.e.

Dim mySheet As Object

--

"Dick Kusleika" wrote in message ...
MonkeyMan

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

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"MonkeyMan" wrote in message
om...
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 :-)

Access control on a worksheet - help!
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"onedaywhen" wrote in message
om...
Or change the sheet declaration to Object i.e.

Dim mySheet As Object

--

"Dick Kusleika" wrote in message
...
MonkeyMan

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

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"MonkeyMan" wrote in message
om...
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 :-)

Access control on a worksheet - help!
 

Cheers Guys, great help!