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