Retain selection in ActiveX list box

[bv182]

Hi,

I am using ActiveX list box with multiple selection. Now Whenever I close the workbook and then reopen, all the previous selections are gone.

Is there a way to retain selections made in listbox?

 

[Domenic]

No changes! I exactly use the same code! I sent you a message as well. could you please take a look at the workbook and see where is the problem?
Thanks!

Here's a sample workbook for you to look at...

https://onedrive.live.com/redir?resid=41260C1C818928B2!664&authkey=!AJ-kKhkZ4KU5Ays&ithint=file,xlsm

Hope this helps!

 

[niushaz]

I have 5 ActiveX List Boxes and this code only works for ActiveX List Box1. Can you help me for a code for all 5 ActiveX List Boxes?

Thanks!

Place code into ThisWorkbook module.

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] Workbook_BeforeSave([COLOR=Blue]ByVal[/COLOR] SaveAsUI [COLOR=Blue]As[/COLOR] [COLOR=Blue]Boolean[/COLOR], Cancel [COLOR=Blue]As[/COLOR] [COLOR=Blue]Boolean[/COLOR])
    [COLOR=Blue]Call[/COLOR] SaveSelections
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] Workbook_Open()
    [COLOR=Blue]Call[/COLOR] RestoreSelections
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] SaveSelections()
    
    [COLOR=Blue]Dim[/COLOR] arr() [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], i [COLOR=Blue]As[/COLOR] [COLOR=Blue]Long[/COLOR], j [COLOR=Blue]As[/COLOR] [COLOR=Blue]Long[/COLOR]
    
    [COLOR=Blue]With[/COLOR] Sheet1.OLEObjects("ListBox1").Object
        [COLOR=Blue]If[/COLOR] .ListIndex > 0 [COLOR=Blue]Then[/COLOR]
            [COLOR=Blue]For[/COLOR] i = 0 [COLOR=Blue]To[/COLOR] .ListCount - 1
                [COLOR=Blue]If[/COLOR] .Selected(i) [COLOR=Blue]Then[/COLOR]
                    j = j + 1
                    [COLOR=Blue]ReDim[/COLOR] [COLOR=Blue]Preserve[/COLOR] arr(1 [COLOR=Blue]To[/COLOR] j)
                    arr(j) = i
                [COLOR=Blue]End[/COLOR] [COLOR=Blue]If[/COLOR]
            [COLOR=Blue]Next[/COLOR]
            Names.Add Name:="Selections", RefersTo:=arr, Visible:=[COLOR=Blue]False[/COLOR]
        [COLOR=Blue]End[/COLOR] [COLOR=Blue]If[/COLOR]
    [COLOR=Blue]End[/COLOR] [COLOR=Blue]With[/COLOR]
    
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

[COLOR=Blue]Private[/COLOR] [COLOR=Blue]Sub[/COLOR] RestoreSelections()
    
    [COLOR=Blue]Dim[/COLOR] arr [COLOR=Blue]As[/COLOR] [COLOR=Blue]Variant[/COLOR], i [COLOR=Blue]As[/COLOR] [COLOR=Blue]Integer[/COLOR]

    arr = [Selections]
        
    [COLOR=Blue]With[/COLOR] Sheet1.OLEObjects("ListBox1").Object
        [COLOR=Blue]For[/COLOR] i = 1 [COLOR=Blue]To[/COLOR] [COLOR=Blue]UBound[/COLOR](arr)
            .Selected(arr(i)) = [COLOR=Blue]True[/COLOR]
        [COLOR=Blue]Next[/COLOR]
    [COLOR=Blue]End[/COLOR] [COLOR=Blue]With[/COLOR]
    
[COLOR=Blue]End[/COLOR] [COLOR=Blue]Sub[/COLOR]

 

[niushaz]

Thanks but I can not see the codes!

 

[niushaz]

I can not see the codes!

Here's a sample workbook for you to look at...

https://onedrive.live.com/redir?resid=41260C1C818928B2!664&authkey=!AJ-kKhkZ4KU5Ays&ithint=file,xlsm

Hope this helps!

 

[Domenic]

I can not see the codes!

When you've clicked on the link and the workbook opens in your browser, go to the far right side of the menu under "Sign In", and click on the button with 3 dots. Then select Download to download the file to your computer. Then open the file from your computer.