Get selected values from listbox as text

[Dampa88]

Dears,

I have a macro that is writing to the first columns all the values selected from a List Box.

Private Sub GO_Click()
    Dim itemsselected As String
    Dim x As Long

    Columns("A:A").Select
    Selection.ClearContents
       
    Range("A1").Select
    For x = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(x) = True Then

        ActiveCell = ListBox1.List(x)
        ActiveCell.Offset(1, 0).Select
        ListBox1.Selected(x) = False 'unselect
    End If
    Next x

   
End Sub

The problem is that values are represented by numbers with leading zeros (to reach 5 digits, eg. 00010, 01245).
However, when values are written in the column A, they are converted to number (10, 1245).

Is there anyway to prevent this?
As a workaround, I'm using a formula to add again leading zeros, but I'm sure there's a quicker way.

Thanks a lot,
D

 

[Joe4]

See if this works:

Private Sub GO_Click()
    Dim itemsselected As String
    Dim x As Long

    Columns("A:A").ClearContents
    
'   Pre-format column A as Text
    Columns("A:A").NumberFormat = "@"
       
'   Loop through list and write to column A
    For x = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(x) = True Then
            Range("A" & x + 1) = ListBox1.List(x)
            ListBox1.Selected(x) = False 'unselect
        End If
    Next x
   
End Sub

I also cleaned up a few things for you. Your code will go faster if you remove the select statements, which are not necessary.

 

[Dampa88]

Hi Joe,

Thank you for this, I'm confirming it's working! Easy solution, I haven't thought about that.

Regards,
D

 

[Joe4]

You are welcome.
Glad I was able to help!