ListFillRange

[ranswrt]

I have the following code in a procdure:

Sheets("Home").OLEObjects("ListBox1").ListFillRange =
Range("wrkshtrng").Address

The listbox window is blank. I have been using:

Sheets("Home").OLEObjects("ListBox1").ListFillRange = "wrkshtrng"

But I have been having problems. I don't know if that is the cause of the
problems or not. Why is the first code not working?
Thanks

 

[Rick Rothstein \(MVP - VB\)]

After I named a range "wrkshtrng", this line...

Sheets("Home").OLEObjects("ListBox1").ListFillRange =
Range("wrkshtrng").Address

worked fine for me.

Rick

 

[ranswrt]

"wrkshtrng" is on a different sheet than the sheet with the listbox in, does
that make a difference?

 

[Dave Peterson]

Did you try all the other suggestions at your earlier post?

Just curious.

 

[ranswrt]

Yes

Did you try all the other suggestions at your earlier post?

Just curious.

 

[ranswrt]

I call this procedure alot when I'm running other procedure:

Sub stopautocalc()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub

Then I turn them back on again. Could this have anything to do with it?

 

[Dave Peterson]

I don't think so.

You really tried:

Sheets("Home").OLEObjects("ListBox1").ListFillRange _
= Range("wrkshtrng").Address(external:=true)

Or being more specific:

Sheets("Home").OLEObjects("ListBox1").ListFillRange _
= worksheets("somesheet").Range("wrkshtrng").Address(external:=true)

 

[ranswrt]

I changed
Sheets("Current DB") to worksheets("Current DB")
and added (external:=True)
That seemed to have fixed the problem.
Thanks for your help