Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    HI,

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

    Thisworkbook.Worksheets("MovedParts").ComboBox1.Li stFillRange = "DRTables.xls!PartList"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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/
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    New Lounger
    Join Date
    Mar 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    MCP- Most Called Player?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Mar 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    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

  11. #11
    New Lounger
    Join Date
    Mar 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    I'm afraid it's really Mighty Cheap Player

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: HairPulling ComboBox ListFillRange Problem (2000/SR1)

    hee hee!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •