Excel VBA - ActiveX Listbox Behavior - Selections being cleared

Question

I am trying to figure out an issue with a pair of ActiveX Multi-select listboxes.

These listboxes are on different sheets, but I have them set up to sync values on mouse-up between the two.
These call a function to sync the two, and then update a cell to preserve the selections.
Because writing to the sheet causes Excel to calculate, I then have to restore these selections.

For some reason, everything seems to work fine except when I run it from the second box on the "Summary" sheet.

In short, running the MouseUp event code for the second box using a break-point with the debugger works. Just triggering the event without a break-point will have everything work, but then after a second or two the selections will clear.

I can confirm that re-running the code to restore the selections will work properly from then on. All other behavior at this time works as expected. It is only when the MouseUp event is triggered (without a breakpoint) that it seems to cause some event that clears the selections. (I now believe this to be the sheet calculation after re-enabling events... though I would think that this would happen in debugging too...)

Update 1: I have found a workaround related to the sheet of where the selection is stored being calculated after setting EnableEvents to true. I thus have inserted Sheet(...).Calculate as shown below, and things work as expected now. This behavior seems quite odd, so I wanted to see if anyone else had encountered something like this. Why does the sheet recalculate even though the changes were made when events are disabled? And for some reason, disabling calculations temporarily doesn't seem to do it either, although I haven't spent too much time on testing this idea (it seemed to create more issues than it fixed).

Update and Solution:

The issue turned out to be different than I originally thought. I have figured out that it is unrelated with the syncing, and is actually related to the fact that the listboxes are on different sheets. Both refer to a dynamic range (which I forgot to mention), and this is actually related to how dynamic ranges recalculate (thus resetting the list box). Initially when I had one box, I worked around this by not passing the box the named-range, but the addresses of the range each time it would be updated due to importing data (or tweaking with in place controls).

This box is on the same page as the dynamic range while the other is not, which meant that I could not pass the addresses (the ActiveX control does not take sheet references in "Linked Cell"), so I had settled on referring to the named range. This reintroduced the issues that I had seen, where many of my workarounds handled other symptoms until this issue manifested.

I was able to resolve this by creating another "stagnant" named range that I would update the references for to point to the original named range when it was updated by my other macros, thus stopping it from recalculating (and resetting my listboxes). Hope this is helpful to someone.

box1: LegSections2Check

Private Sub LegSections2Check_MouseUp(ByVal Button As Integer, _ 
                     ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call SyncChoices(Sheets("Loads"))
    Retain_Selections SetUp:=True
    Retain_Selections SetUp:=False
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

box2: LegSections2Check2

Private Sub LegSections2Check2_MouseUp(ByVal Button As Integer, _ 
                     ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Call SyncChoices(Sheets("Summary"))
    Retain_Selections SetUp:=True

    'Sheets("Loads").Calculate     '*** This seems to resolve the issue, although _
                                   ' I can't seem to resolve it by setting_ 
                                   ' Application.Calculation to manual in the functions _
                                   ' (this actually broke more things, haha)   

    Retain_Selections SetUp:=False
    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

SyncChoices (detects which sheet the editted box is on and updates the other box):

Sub SyncChoices(ByVal mySheet As Worksheet)
Dim i As Integer
    If mySheet.Name = "Loads" Then
        With ActiveWorkbook.Sheets("Loads").LegSections2Check
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    ActiveWorkbook.Sheets("Summary").LegSections2Check2.Selected(i) = True
                ElseIf Not .Selected(i) Then
                    ActiveWorkbook.Sheets("Summary").LegSections2Check2.Selected(i) = False
                End If
            Next
        End With
    ElseIf mySheet.Name = "Summary" Then
        With ActiveWorkbook.Sheets("Summary").LegSections2Check2
            For i = 0 To .ListCount - 1
                If .Selected(i) Then
                    ActiveWorkbook.Sheets("Loads").LegSections2Check.Selected(i) = True
                ElseIf Not .Selected(i) Then
                    ActiveWorkbook.Sheets("Loads").LegSections2Check.Selected(i) = False
                End If
            Next
        End With
    End If
End Sub

The Retain_Selections function:

Sub Retain_Selections(ByVal SetUp As Boolean)
'Dim myListBox As ListBox
Dim i As Integer
Dim MyVals As String
Dim Selections() As String
Dim offset As Integer
Dim myCount As Integer

    With ActiveWorkbook.Sheets("Loads").LegSections2Check
        RefColumn = ActiveWorkbook.Sheets("Loads").Range("RefCol").Column
        If SetUp Then
            ActiveWorkbook.Sheets("Loads").Cells(2, RefColumn).Value = GetSelections()
        Else
            Selections = Split(ActiveWorkbook.Sheets("Loads").Cells(2, RefColumn).Value, ", ")
            ClearSelectedSections
            myCount = .ListCount
            ActiveWorkbook.Sheets("Summary").LegSections2Check2.ListFillRange = "LegsSections"
            If UBound(Selections) > 0 Then If Selections(UBound(Selections)) * 1 > myCount - 1 Then offset = 1: ExtraRow = True
            For i = 0 To UBound(Selections) - offset
                .Selected(Selections(i)) = True
                ActiveWorkbook.Sheets("Summary").LegSections2Check2.Selected(Selections(i)) = True
            Next i
        End If
    End With

And the GetSelections() function:

Function GetSelections()
Dim i As Integer
Dim MyVals As String
With ActiveWorkbook.Sheets("Loads").LegSections2Check
    For i = 0 To .ListCount - 1
        If .Selected(i) = True Then
            If MyVals = "" Then
                MyVals = i
            Else
                MyVals = MyVals & ", " & i
            End If
        End If
    Next i
End With
GetSelections = MyVals
End Function

And the Code for the sheets' WorkSheet_Activate:

"Loads"

Private Sub Worksheet_Activate()
    Retain_Selections Setup:=False
End Sub

"Summary"

Sub Worksheet_Activate()
    Retain_Selections Setup:=False
End Sub

PeterT PeterT · Accepted Answer · 2016-03-01T18:08:42

Auto-calculation is not turned off when you disable events. Excel refers to a separate workbook-global property to determine if the workbook requires recalculation. So you have to disable/enable the autocalc yourself.

So, two things:

In addition to temporarily disabling events, my "standard" utility function optionally set AutoCalc to manual Application.Calculation = xlManual, just to make sure all my macros complete before allowing calculations.

Also, if your listboxes are on different sheets, there's no reason to re-populate the listbox on the non-active sheet until the user makes that sheet active. In this case, I would use the Worksheet_Activate() event on each sheet to populate the listbox when the user selects that sheet.

Corollary to the second thing: if your code relies on the list of items in the listbox as "the" filter used in parts of your code, consider creating a named range as the data that will populate each listbox. This method means that you're only updating the cell ranges on a worksheet and the listboxes pick that up automagically when activated.

Excel VBA - ActiveX Listbox Behavior - Selections being cleared

Update and Solution:

1 Answers