Excel VBA: Save As triggers Change event in ComboBox

Question

I have an Excel workbook containing some ComboBox controls placed directly on the sheets. These are standard combo boxes from the Forms toolbar.

When the user uses "Save As" to save the workbook with a different name, this triggers the Change event on all the combo boxes, including ones on sheets that aren't active. This seems unreasonable as the selection hasn't actually changed. This causes various undesirable behaviour because of the code in the event handlers. The event isn't triggered on a simple "Save".

Google suggests this is a known problem in Excel. There are rumours that it's caused by using a named range as the ListFillRange for the combo box, which I have done, although it's not a volatile name. I'm looking for a way to prevent this happening with minimal changes to the code and the spreadsheet. Does anyone have a proven solution?

Answer 1

You could set a flag in the Workbook's BeforeSave event and then check that flag before processing a change event in each of the combo boxes. There does not seem to be an AfterSave event, so you would need to clear the flag after checking it within the combo box change events. The flag would need to be more than a simple boolean since it could not be turned off until all combo box change events were processed. Here's some sample code:

Public ComboBoxChangeCounter As Integer

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Const NumOfComboBoxChangeEvents As Integer = 5
    ComboBoxChangeCounter = NumOfComboBoxChangeEvents
End Sub

Function JustSaved() As Boolean
    If ComboBoxChangeCounter > 0 Then
        ComboBoxChangeCounter = ComboBoxChangeCounter - 1
        JustSaved = True
    End If
End Function

Private Sub Combo1_Change()
    If JustSaved Then Exit Sub
    'Your existing code '
    ' ... '
    ' ... '
    ' ... '
End Sub

I set the number of combo box change events as a constant, but there may be some way for you to determine that number programmatically. This workaround does require adding code to every combo box change event, but it should be easy as all you need to do is copy and paste the line If JustSaved Then Exit Sub at the beginning of each event.

This workaround assumes that the Workbook BeforeSave event will get called prior to the combo box change events. I don't know for a fact if that's the case.