Saving values from Multi-select List Box

[Liz]

Hi -
I have the following code that saves the values from a multi-select list box
across multiple cells. However, I would like to save the mulitple values to
one cell, using a comma or alt-enter delimiter instead. How can I modify
this code to save the selection of multiple values within one cell?

Dim DestCell As Range
Dim iCtr As Long

With Sheets("View My Requests")
Set DestCell = .Range("G" & inextrow)
End With

With Me.lstSecondProd
DestCell.Resize(.ListCount, 1).ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
DestCell.Value = .List(iCtr)
Set DestCell = DestCell.Offset(0, 1)
End If
Next iCtr
End With

In addition, I would like to also have the multi-select list box read the
multiple values from a cell (or read the values in a range) and use those
values as the default selections in the multi-select list box when the dialog
is shown. Is it possible to do this using a single cell? It is possible to
do this from a range of cells?

Thank you,
Liz

 

[OssieMac]

Hi Liz,

Try the following 2 subs. Alt/enter is actually line feed chr(10) or vbLf.

For testing purposes I just run the code from command buttons.

Private Sub CommandButton1_Click()
'Create single string with lline feeds
'from listbox selections.
Dim DestCell As Range
Dim iCtr As Long
Dim inextRow As Long
Dim strTemp

inextRow = 2 'Included for testing

With Sheets("View My Requests")
Set DestCell = .Range("G" & inextRow)
End With

With Me.lstSecondProd
DestCell.ClearContents

For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
strTemp = strTemp & .List(iCtr) & vbLf
End If
Next iCtr
End With

'Remove trailing linefeed
strTemp = Left(strTemp, Len(strTemp) - 1)

DestCell.Value = strTemp

'If you want to autofit col width and row height _
then do in following order.
'1. Set column width to greater than _
is likely to be after autofit column.
'2. Set Wrap text.
'3. Autofit column.
'4. Autofit row.

End Sub

'*******************************
'Next answer

Private Sub CommandButton2_Click()
'Set default listbox selections
Dim DestCell As Range
Dim iCtr As Long
Dim inextRow As Long
Dim strTemp

inextRow = 2 'Included for testing

With Sheets("View My Requests")
Set DestCell = .Range("G" & inextRow)
End With

strTemp = DestCell.Value

With Me.lstSecondProd
For iCtr = 0 To .ListCount - 1
If InStr(1, strTemp, .List(iCtr)) > 0 Then
.Selected(iCtr) = True
End If
Next iCtr
End With

End Sub

 

[Liz]

Both of these macros worked perfectly....they help manage the results of a
multi-select list box, which I found very confusing how to work with before
your help. Thank you so much for your guidance! It is greatly appreciated!

Best Regards,
Liz