activeX multiselect list doesn't save when exiting

[jab973]

I have created a small macro to display a checklist that can by checked off and says "Complete" once all items have been checked. I created this using ActiveX Listbox with multiselect. However, when the file is saved and reopened, all the check marks clear out. Is there a way to fix this so they stay checked? Maybe with OLEObject, but I don't understand how to use that?

Thanks for any assistance!

Private Sub listbox1_change()
Call checklist
End Sub
Private Sub ListBox1_Click()
Call checklist
End Sub




Sub checklist()
Sheet1.Cells(1, 1).Clear


With Sheet1.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) = False Then Sheet1.Cells(1, 1) = "NOT Complete"
Next i
If Sheet1.Cells(1, 1) = "" Then Sheet1.Cells(1, 1) = "Complete"
End With
End Sub

 

[Fluff]

One option is to store the values on a sheet.
I've used col Z on the same sheet as the listbox

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Cnt As Long

    With Sheet1.ListBox1
        For Cnt = 0 To .ListCount - 1
            Sheet1.Range("Z" & Cnt + 1) = .Selected(Cnt)
        Next Cnt
    End With

End Sub


Private Sub Workbook_Open()
    Dim Cnt As Long

    With Sheet1.ListBox1
        For Cnt = 0 To .ListCount - 1
            .Selected(Cnt) = Sheet1.Range("Z" & Cnt + 1)
        Next Cnt
    End With

End Sub

 

[jab973]

Thanks! That works perfectly...

 

[Fluff]

Glad to help & thanks for the feedback