Help with VBA Code

[Keith]

I have a form with a multi select list box. I need the output to be a list of rows selected seperated by a comma. The code that I have puts a comma at the start of the line i.e , Secretary, Treasurer

Private Sub Command2_Click()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim Res As String
    Res = ""
    Set frm = Forms!frmSelectOffice
    Set ctl = frm!OfficeList
    For Each varItm In ctl.ItemsSelected
        Debug.Print ctl.ItemData(varItm)
        Res = Res & ", " & ctl.ItemData(varItm)
        Debug.Print Res
    Next varItm
End Sub

 

[jdraw]

What does debug.print res give you?

What exactly is your question?

 

[Keith]

Debug.print Res lists the items selected but with a comma at the begining of the line. The result I need is the items selected seperated by a comma

 

[Keith]

I changed this line Res = Res & ", " & ctl.ItemData(varItm)
To Res = Res & ctl.ItemData(varItm) & ", "
and the result is Secretary, Treasurer,
I dont want the comma at the end if that makes sense.

 

[jdraw]

This line is telling it to put in the comma, then a value

Res = Res & ", " & ctl.ItemData(varItm)

Perhaps you could try:

Res = Mid(Res,2)

after the Next varItm line

This would ignore the first comma, and give you a line starting with "secretary" fter the string of values was finished.

 

[Keith]

Thats cracked thanks.
used Res = Mid(Res,3) which got rid of the leading space as well.