error 13 when setting worksheet listbox

[Amedee Van Gasse]

Sorry for the multipost but my message didn't appear in the other
group and microsoft.public.excel.programming is more appropriate
anyway.


A code snippet:

Function FillYearMonth(ByVal theWorkSheet As String, ByVal
thePivotTable As String, ByVal thePageField As String)
Dim ws As Worksheet
Set ws = Worksheets(theWorkSheet)
Dim pt As PivotTable
Set pt = ws.PivotTables(thePivotTable)
Dim pf As PivotField
Set pf = pt.PivotFields(thePageField)
Dim lb As ListBox
Set lb = Worksheets(theWorkSheet).lstYearMonth
Call FillListBox(pf, lb)
End Function

I keep getting error 13 after executing the line
Set lb = Worksheets(theWorkSheet).lstYearMonth

I checked and there really is a ListBox called lstYearMonth on the
worksheet (on ever worksheet there is a ListBox named lstYearMonth)


Can anyone point me what is wrong in this code?

 

[Jim Thomlinson]

While lstYearMonth may be an embedded object in the sheet you can not
directly reference it as you have since your code does not make the
connection until run time. To directly reference the object you would need to
have the actual sheet where you have the object so that the connection is
there at design time... Try this

set lb = Worksheets(theWorkSheet).oleobject("lstYearMonth")

Also declare your variables with lb as type OLEObject