How to Concat Multiple Listbox Selections?

Hi,
I am an Access guy, building an Excel app and hope someone can help. I need to collect multiple variables that would be entered into a listbox and concatenate them into a single string. An example of the exact functionality I want is located here: http://code.google.com/apis/analytic...aExplorer.html. If you go to "dimensions" a drop down opens and you can check your selections and when you are done, they are all displayed in a single string. How can I do this? Should I should I do this in a workbook or would it be better to create this in a VB Form? Any help would be greatly appreciated!
Thanks!

Thanks, got it working.....you saved me a lot of time!!
But....I know this is scope creep, but.....

Is there a way to list my selections in the order they were selected? Picky, I know, but its actually necessary for this project. I did this in Access by appending each individual selection to a table and deleting when they were deselected. Would the approach here be similar or is there a better way? Thanks!

Originally Posted by AccessGeek

Is there a way to list my selections in the order they were selected? Picky, I know, but its actually necessary for this project. I did this in Access by appending each individual selection to a table and deleting when they were deselected. Would the approach here be similar or is there a better way? Thanks!

Scope creep indeed

Well, I'm not going to write it due to the time involved but maybe I can set you in the right direction.

There is no built-in way to determine order clicked. You will have to code it from the ground up. You will have to keep an array of what the user clicked on, and keep it up to date after each click. Here is how you tell if the user clicked on your list (for some reason the Click event does not happen on a multiselect listbox):

Private Sub ListBox1_MouseUp(ByVal Button As Integer, _
                               ByVal Shift As Integer, _
                               ByVal X As Single, _
                               ByVal Y As Single)
   With ListBox1
      Dim s As String
      Dim i As Long
      For i = 0 To .ListCount - 1
         If .Selected(i) Then
            s = s & .List(i) & ","
         End If
      Next i
   End With
   
   MsgBox s

End Sub

You will need an array. Every time the user releases the mouse button, it means they have either selected an item, or deselected an item. You will have to compare the selected items to the array to figure out what happened. If the user selected an item, you need to add that to the array; otherwise if they removed an item, remove it from the array.

Is the content of this listbox dynamic? If it's static then an alternative would be to use a set of checkboxes. Each checkbox has a Click event. You would still have to keep an array to keep track of what order things are clicked in, but you wouldn't have to iterate through a list each time to figure out what changed.

Yes, the variables are static so checkboxes would work perfectly. I will have to mess around with your code to see how I can maintain order. Thanks for your help......I owe you a beer!