How to determine the Order in Which Selections are Made in listbox by the user?

Hi ,
I am trying to determine the Order in Which Selections are Made in listbox by the user ( Excel VBA), So far am able to populate selected into column A but selection order is missing,  below is the code  - also attached file for your reference.
Private Sub CommandButton2_Click()

   Dim msg As String
   For X = 0 To ListBox1.listCount - 1
   If ListBox1.Selected(X) = True Then
      msg = msg & "," & ListBox1.List(X) & vbCrLf
   End If
   Next X
   ThisWorkbook.Sheets("Sheet2").Range("A1").Value = msg
      Dim X1 As Variant
X1 = Split(Range("A1").Value, ",")
On Error Resume Next
Range("A2").Resize(UBound(X1) - LBound(X1) + 1).Value = Application.Transpose(X1)
Cells.WrapText = False
ThisWorkbook.Sheets("Sheet2").Range("B2").Value = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet2").Range("A3:A10"))
End Sub

thanks a lot for the help
Raghavendra BBusiness Operations AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To give you an idea about how you can get the selected ListBox items in the order they were selected, I am attaching a sample file with a sample UserForm with a ListBox and CommandButton to Submit the data on Sheet1.
On Sheet1, click the button Show UserForm and select the items from the list in the order of your choice and click the Submit button and it will place the selected items in the same order you selected them in Column A on Sheet1.
You will have to adopt the same approach in your file.

Here are the code which are placed on the UserForm Module.

Dim dict As Object

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Range("A1").Value = "Months"
ws.Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.items)
End Sub

Private Sub ListBox1_Change()
Dim i As Long
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        If Not dict.exists(i) Then
            dict.Item(i) = Me.ListBox1.List(i)
        End If
    End If
Next i
End Sub

Private Sub UserForm_Initialize()
Set dict = CreateObject("Scripting.Dictionary")
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raghavendra BBusiness Operations AnalystAuthor Commented:
Hi Subodh,
Thank you very much, it  worked like a charm :) , just wow.
Now i can build rest of my coding part easily based on the user selection, !!!

Thanks a lot for very quick turn around time with accurate solution,

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Raghu! Glad it worked as desired.
Thanks for the feedback.

BTW I am surprised, why did you award only 500 bonus points?
If you are not aware of how to award bonus points, just click the Report Question link under your question and request to reopen the question so that you can award the 1000 bonus points. You can ask for the help to know the process of awarding the bonus points and a moderator will assist you regarding this.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.