Saving & Retrieivng Multi Select ListBox Values

[mini12]

Hey Guy and Dolls,

I have a problem regarding multi selection listboxes, currently I have a code that transfers the selected values from the listbox to a worksheet and then is used by formulas.

The problem is I cannot make the listbox retrieve the previously selected values when I reload the userform. Is there a solution?

The codes I am using are as follows:

Private Sub UserForm_Initialize()

    'Set ListBox ColumnCount to the same as RowSource
    Me.lbColumns.ColumnCount = Range(Me.lbColumns.RowSource).Columns.Count

End Sub

Private Sub TransferValues_Click()
 Dim lItem As Long, lRows As Long, lCols As Long
 Dim bSelected As Boolean
 Dim lColLoop As Long, lTransferRow As Long
    
 'Pass row & column count to variables
 'Less 1 as "Count" starts at zero
 lRows = Me.lbColumns.ListCount - 1
 lCols = Me.lbColumns.ColumnCount - 1
 
    'Ensure they have at least 1 row selected
    For lItem = 0 To lRows
        'At least 1 row selected
        If Me.lbColumns.Selected(lItem) = True Then
            'Boolean flag
            bSelected = True
            'Exit for loop
            Exit For
        End If
    Next
     
    'At least 1 row selected
    If bSelected = True Then
        With Sheet3.Range("D1", Sheet3.Cells(lRows + 1, 4 + lCols)) 'Transfer to range
            .Cells.Clear 'Clear transfer range
            For lItem = 0 To lRows
                If Me.lbColumns.Selected(lItem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  lTransferRow = lTransferRow + 1
                    'Loop through columns of selected row
                    For lColLoop = 0 To lCols
                       'Transfer selected row to relevant row of transfer range                       .Cells(lTransferRow, lColLoop + 1) = Me.lbColumns.List(lItem, lColLoop)
                    Next lColLoop
                End If
            Next
        End With
        
    Else ' NO listbox row chosen
         MsgBox "Nothing chosen", vbCritical
    End If

End Sub

Kind Regards

mini12

[royUK]

You would need to store the selected items' listindexes in a sheet

[mini12]

Hey royUK,

Thanks for replying, my mind is a blank at the mo so could you give me an example with storing the selected items' listindexes?

Kind Regards

mini12