VBA Code Runs fine while workbook is open, then I get errors when I close my workbook

  • Hello - forgive me if I have done this wrong as this is my first post!


    I have a workbook with some simple code that seems to runs fine, but when I go to close the workbook - even if I don't run the code - I get one of 3 errors:


    1) "ByRef argument type mismatch" - I get this if I don't run the code or I just choose a location

    2) "Microsoft Excel has stopped working" - this happens randomly and I usually "Restart the program"

    3) "Method 'Sheets' of Object'_Global' Failed error" - I can't seem to recreate this error now that I am writing this, but would love to know how to fix it in the future


    My Workbook originally had 7 sheets, and I added a sheet at the front with a Combo Box, Check Box, List Box and Command Button - all created from the ActiveX Controls available

    • The Combo Box is a list of locations (Property_ComboBox)
    • The List Box is a multi-select list of sections on our P&L Report (PNLSection_ListBox)
    • The Check Box just selects/deselects ALL the sections in the List Box
    • The Command Button hides the sections of the report (a separate sheet in the workbook) that are not selected in the List Box
    • The source data for the List Box and Combo Box are named ranges on my sheet
    • I've put all my VBA coding on the new sheet I created (as opposed to creating a Module)
    • The Workbook may contain links to other files, but I am not trying to use these links in any way with the code


    Here is the code for the Combo Box - when the location is changes in the Combo Box, the List Box is updated with selections that apply to that location


    Sub Property_ComboBox_Change()

    Call ShowDefaultList(Property_ComboBox, PNLSection_ListBox)

    End Sub


    Sub ShowDefaultList(cBox As ComboBox, lBox As MSForms.ListBox)

    Dim i As Integer

    Dim PropertyColRef As Integer

    Dim DefaultRange As Range


    Me.Activate

    Set DefaultRange = Me.Range("PNL_SECTIONS") 'PNL_SECTIONS is a named range on the spreadheet that has T/F values based on the location

    PropertyColRef = cBox.Value 'PropertyColRef is the column number of PNL_SECTIONS containing the T/F values that is associated with the location


    With lBox

    For i = 0 To lBox.ListCount - 1

    If DefaultRange(i + 1, PropertyColRef) Then

    lBox.Selected(i) = True

    Else

    lBox.Selected(i) = False

    End If

    Next

    End With

    End Sub


    This appears to work fine ... I can constantly change my location selection and the selected items in the List Box are updated without any issue. Additionally, I can manually make selections in the List Box, or click on the Check Box to select/deselect everything and then use the Combo Box to select a location again and it all works fine.


    Once I save my file and go to close it, the "ByRef argument type mismatch" error pops up and my passing of the List Box seems to be the issue:




    I've been searching the internet for hours trying to figure this out ... I've scrolled through the FAQ here and the first few pages of help in this forum, but I am stumped! I am hoping it is something simple!...



    As for the "Method 'Sheets' of Object'_Global' Failed error" error, I changed the use of Sheets to Worksheets and for now it seems to be ok, but I suspect the problem above may just be happening first and if it were fixed, then this sheets error will come back - I would love to know how to avoid this error too!



    All help will be greatly appreciated!


    Thank you!

    Maria