How to use listbox items as parameter values in VBA Excel

Hi,
1.  multy select listbox...
Question: If I select 2 or more listbox items how can i transform the same and create array with comma separation....
Here is example:
Those are listbox items:
First
Second
Third
Fourth

After I select (for example) second,third and fourth (and e.g. push the command button) I would like to create array as follows
'Second','Third','Fourth' and pass the same into another Sub as parameter


Many thanks for help...

Regards..
LVL 1
alsamAsked:
Who is Participating?
 
kgerbConnect With a Mentor Chief EngineerCommented:
Here you go

Kyle
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
Call CreateString
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To 10
    Me.ListBox1.AddItem "Contents" & i
Next i
End Sub

Sub CreateString()
Dim s As String, i As Long
With UserForm1.ListBox1
    For i = 1 To .ListCount
        If .Selected(i - 1) = True Then s = s & "," & .List(i - 1)
    Next i
End With
s = Right(s, Len(s) - 1)
Debug.Print s
Call AnotherSub(s)
End Sub

Sub AnotherSub(s As String)
MsgBox "Here is your string: " & s, vbOKOnly, "Your String"
End Sub

Open in new window

Q-27394756-RevB.xlsm
0
 
kgerbChief EngineerCommented:
Please take a look at the attached example workbook.  I made the assumption that your listboxes were imbedded on a worksheet and not on a user form.  If they are on a form we can change things slightly.

Kyle
Sub CreateString()
Dim s As String, s1 As String, s2 As String, s3 As String, s4 As String

With Worksheets("Sheet1")
    s1 = .ListBox1
    s2 = .ListBox2
    s3 = .ListBox3
    s4 = .ListBox4
End With
s = s1 & "," & s2 & "," & s3 & "," & s4
Call AnotherSub(s)
End Sub

Sub AnotherSub(s As String)
MsgBox "Here is your string: " & s, vbOKOnly, "Your String"
End Sub

Open in new window

Q-27394756-RevA.xlsm
0
 
alsamAuthor Commented:
Thanks,
Following to your assumption Please be informed that I have only one multy select listbox on the Userform from which I would like to create string value depending of selected items in the same...
Your example is very good but I would like you to help me adjust the same concerning the info above...
thanks
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
kgerbChief EngineerCommented:
Ok, no problem.  Hold please... :-)

Kyle
0
 
alsamAuthor Commented:
Thanks a lot
0
 
kgerbChief EngineerCommented:
You're welcome:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.