none
Excel 2010 VBA Run time Error: " Method 'ListFillRange' of object '_OLEObject' failed ", appears only whenever the saved workbook reopen RRS feed

  • Question

  • Hi Team,

    I have the below VBA code in an excel 2010 sheet, in th event ComboBox1_Change().

    Purpose is to change the list box values(ListFillRange) according to the combo box selection.

    It works fine, but when I save & close the workbook and reopen it gives me run time error " Method 'ListFillRange' of object '_OLEObject' failed ", at the following code

    eg:lb.ListFillRange = c_rdc    'Named Range "DC"(Note: while saving the workbook "DC" is already selected in combo box)

    Error with screenshot is mentioned below the code.

    NOTE-1: I will get this error first time only whenever I reopen the saved workbook and after stopping VBA Debugger, afterwards as many times I change the combo box selection it works fine.

    NOTE-2: If I don't save the workbook and reopen then I won't get error, it stops me with error whenever I save the workbook and reopen

    Thanks in advance for your help and support.

    Please find below the code & error screenshot for your reference.

        '// Get selected value from combo box
        Set wbs = ThisWorkbook
        Set shh = wbs.Worksheets(c_shh) 'c_shh = "Header"
        Set cb = shh.OLEObjects(c_cb1)  'cb As OLEObject, c_cb1 = "ComboBox1"
        pf = cb.Object.Value    'pf As String
        
        '// Set the listbox values according to the combo box option selected
        Set lb = shh.OLEObjects(c_lb1)  'lb As OLEObject, c_lb1 = "ListBox1"
        
        Select Case pf
            Case c_dc
                lb.ListFillRange = c_rdc    'Named Range "DC"
                dc_disable = True
            Case c_ds
                lb.ListFillRange = c_rds    'Named Range "DS"
                ds_disable = True
            Case c_dt
                lb.ListFillRange = c_rdt    'Named Range "DT"
                dt_disable = True
            Case c_os
                lb.ListFillRange = c_ros    'Named Range "OS"
                os_disable = True
            Case c_oss
                lb.ListFillRange = c_ross   'Named Range "OSS"
                oss_disable = True
            Case c_pty
                lb.ListFillRange = c_rpty   'Named Range "PTY"
                pty_disable = True
            Case c_prc
                lb.ListFillRange = c_rprc   'Named Range "PRC"
                prc_disable = True
            Case c_prj
                lb.ListFillRange = c_rprj   'Named Range "PRJ"
                prj_disable = True
            Case c_svr
                lb.ListFillRange = c_rsvr   'Named Range "SVR"
                svr_disable = True
            Case c_tp
                lb.ListFillRange = c_rtp    'Named Range "TP"
                tp_disable = True
            Case c_tsp
                lb.ListFillRange = c_rtsp   'Named Range "TSP"
                tsp_disable = True
            Case c_ug
                lb.ListFillRange = c_rug    'Named Range "UG"
                ug_disable = True
        End Select

    Tuesday, August 12, 2014 3:59 PM

Answers

  • Hi,

    I have checked in other excel forums and got the solution for my problem and now it is working fine without any issues.

    Issue faced: Sheet with ActiveX controls gives error with "OLE Objects" when we re-open the saved workbook.

    Cause: Having excel generated names(like "ListBox1", "ListBox2", "ComboBox1"..etc) for the activex controls on the sheet. Excel changes names of the activex controls whenever we re-open the workbook.

    Solution: For the activex controls on the sheet change the generated names to new custom names(like "Lb_Project", "Lb_Cost", "Cb_Budget"..etc)

    Thanks

    Prathap

    • Marked as answer by PPrathap Wednesday, August 13, 2014 2:35 PM
    Wednesday, August 13, 2014 2:35 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support

    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Wednesday, August 13, 2014 1:57 AM
  • Hi,

    I have checked in other excel forums and got the solution for my problem and now it is working fine without any issues.

    Issue faced: Sheet with ActiveX controls gives error with "OLE Objects" when we re-open the saved workbook.

    Cause: Having excel generated names(like "ListBox1", "ListBox2", "ComboBox1"..etc) for the activex controls on the sheet. Excel changes names of the activex controls whenever we re-open the workbook.

    Solution: For the activex controls on the sheet change the generated names to new custom names(like "Lb_Project", "Lb_Cost", "Cb_Budget"..etc)

    Thanks

    Prathap

    • Marked as answer by PPrathap Wednesday, August 13, 2014 2:35 PM
    Wednesday, August 13, 2014 2:35 PM