HairPulling ComboBox ListFillRange Problem (2000/SR1)

[knauf]

Hello,

I'm having a nasty problem with the ListFillRange property of a ComboBox.

I have a workbook with two sheets which loads another workbook containing two sheets of data when it is opened. One data sheet is supposed to be the data for the ComboBox in the first workbook.

The problem is that when the forms load clicking on the ComboBox shows empty data. If I go to the properties sheet of the ComboBox and cut (CTRL-X) the ListFillRange then immediately paste it back the ComboBox works fine. Go Figure!

Here is the code from my Open() in the first workbook:

Private Sub Workbook_Open()

Dim twbName As String

twbName = ThisWorkbook.Name

DisplayAlerts = False

Workbooks.Open Filename:="DRTables.xls"

Windows(twbName).Activate
Worksheets("Report").Activate


' I can't make the line below work. I get a runtime 424 - object expected
' or something like that...

' Worksheets("Report").ComboBox1.ListFillRange = DRTables.xls!PartList

DisplayAlerts = True

End Sub


Does anybody have a clue what's happening here?

Thanks for any help.

Dan Knauf

[pieterse]

Hi Knauf,

<<' Worksheets("Report").ComboBox1.ListFillRange = DRTables.xls!PartList
>>

You need to tell VBA which workbook this sheet belongs to:

Thisworkbook.Worksheets("Report").ComboBox1.ListFi llRange = DRTables.xls!PartList

(assuming the combobox is in the same workbook as this code)

[knauf]

Hi Jan,

Thanks for the post.

Yes, the ComboBox is in the same workbook as the code.

I still get the 'runtime error 424 - object expected' error using your suggestion.

Actually, I left the wrong line in there too. It should actually be:

Thisworkbook.Worksheets("MovedParts").ComboBox1.Li stFillRange = DRTables.xls!PartList

What's really been bugging me is that I shouldn't even need that line of code. Setting the ListFillRange via the property sheet should be all I need to do.

Knauf

[pieterse]

HI,

I suspect the syntax is the problem. Does this help:

Thisworkbook.Worksheets("MovedParts").ComboBox1.Li stFillRange = "DRTables.xls!PartList"

[knauf]

Hi Jan,

I see how you got that Most Valuable Player award! [img]/forums/images/smilies/wink.gif[/img]

That solution works.

Nothing in any of my reference material (including over 100,000 newsgroup posts) indicates that quotes should be used unless I'm addressing an external file.

Thank you very much.

Knauf

[pieterse]

Hi Knauf,

<<I see how you got that Most Valuable Player award! [img]/forums/images/smilies/wink.gif[/img]>>
Thanks.

BTW: It is Most Valuable Professional.

See:

http://www.mvps.org/

[knauf]

Hello Jan,

I had a finger firmly in my cheek when I typed Most Valuable Player. I am a stagnated MCP myself so I am very well aware of the fact that the P is for Professional.

I meant absolutely no disrespect. I know how much work those M*'s are to get.

Thanks again for the great help,

Dan Knauf

[gwhitfield]

MCP- Most Called Player?

[pieterse]

Apologies accepted, but unnecessary <smile>.

I didn't expect you to mean to be disrespectful, just thought you didn't know.

So what's an MCP?

[knauf]

Microsoft Certified Professional. I don't know if it's even available any more. It was the bottom of the M* tier of certification when I got it. The next logical step would have been MCSE.

However, I came across so many MCSE's who knew what the book said and not what the PC said that I got discouraged with the whole process and never pursued any further certification. I just learn and use whatever I need.

I understand Microsoft has figured out that they had a problem and made the tests require some real world knowledge nowadays. I haven't taken any tests in the last 3 years so I don't know first hand.

Dan

[knauf]

I'm afraid it's really Mighty Cheap Player

[WebGenii]

hee hee!