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

I did the following in a new workbook with only one sheet, Sheet1, and it seemed to work to diable events before save and then reenable them after. It should bypass the problem you see by mimicing an AfterSave event. This is my event code on Sheet1 (could be OLEObject code, too)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  MsgBox Target.Address & ": " & Target.Value
End Sub

This is my ThisWorkbook code

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  ' To see the change code work before disabling
  ' Should show a message box
  Sheet1.Range("A1") = "Before After Events Off"

  Application.EnableEvents = False
  Application.OnTime Now, "ThisWorkbook.Workbook_AfterSave"

  ' This time it will not show a message box
  ' You will never see this one . . . 
  Sheet1.Range("A1") = "After After Events Off"

End Sub

Private Sub Workbook_AfterSave()
  Application.EnableEvents = True
End Sub

The .OnTime method throws the AfterSave "event" onto the execution queue. It works!

Answer 2

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.