How to save or retain selections of ActiveX list boxes in Excel?

Supposing you have created some list boxes and made selections in the list boxes, however, all selections of these list boxes are gone when close and reopen the workbook. Do you want to retain selections made in list boxes whenever close and reopen the workbook? The method in this article can help you.

Save or retain selections of ActiveX list boxes with VBA code in Excel

Save or retain selections of ActiveX list boxes with VBA code in Excel

The below VBA code can help you saving or retaining selections if ActiveX list boxes in Excel. Please do as follows.

1. In the workbook contains the ActiveX list boxes you want to keep the selections, press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, double-click ThisWorkbook in the left pane to open the ThisWorkbook Code window. And then copy the following VBA code into the code window.

VBA code: Save selections of ActiveX list boxes in Excel

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim I As Long
    Dim J As Long
    Dim K As Long
    Dim KK As Long
    Dim xSheet As Worksheet
    Dim xListBox As Object
    On Error GoTo Label
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    K = 0
    KK = 0
    If Not Sheets("ListBox Data") Is Nothing Then
        Sheets("ListBox Data").Delete
    End If
Label:
    Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "ListBox Data"
    Set xSheet = Sheets("ListBox Data")
    For I = 1 To Sheets.Count
        For Each xListBox In Sheets(I).OLEObjects
            If xListBox.Name Like "ListBox*" Then
                With xListBox.Object
                For J = 0 To .ListCount - 1
                    If .Selected(J) Then
                        xSheet.Range("A1").Offset(K, KK).Value = "True"
                    Else
                        xSheet.Range("A1").Offset(K, KK).Value = "False"
                    End If
                    K = K + 1
                Next
                End With
                K = 0
                KK = KK + 1
            End If
        Next
    Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Private Sub Workbook_Open()
    Dim I As Long
    Dim J As Long
    Dim KK As Long
    Dim xRg As Range
    Dim xCell As Range
    Dim xListBox As Object
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    KK = 0
    For I = 1 To Sheets.Count - 1
        For Each xListBox In Sheets(I).OLEObjects
            If xListBox.Name Like "ListBox*" Then
                With xListBox.Object
                    Set xRg = Intersect(Sheets("ListBox Data").Range("A1").Offset(0, KK).EntireColumn, Sheets("ListBox Data").UsedRange)
                    For J = 1 To .ListCount
                        Set xCell = xRg(J)
                        If xCell.Value = "True" Then
                            .Selected(J - 1) = True
                        End If
                    Next
                    KK = KK + 1
                End With
            End If
        Next
    Next
    Sheets("ListBox Data").Delete
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Now you need to save the workbook as an Excel Macro-enabled workbook. Please click File > Save As > Browse.

5. In the Save As dialog box, select a folder to save the workbook, rename it as you need, select Excel Macro-Enabled Workbook in the Save as type dropdown list, and finally click the Save button. See screenshot:

Please save the workbook every time when you update the list boxes. Then all previous selections will be kept in the list boxes after reopening the workbook.

Note: When saving the workbook, a worksheet named “ListBox Data” will be created automatically at the end of all worksheets of your workbook, please ignore this worksheet because it will disappear automatically when the workbook is closed.