Get selected values from listbox as text

Dampa88

Board Regular
Joined
Apr 28, 2016
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Dears,

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

VBA Code:
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
See if this works:
VBA Code:
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.
 
Upvote 1
Solution
Hi Joe,

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

Regards,
D
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top