[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

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..
0
alsam
Asked:
alsam
  • 4
  • 2
1 Solution
 
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
 
kgerbChief EngineerCommented:
Ok, no problem.  Hold please... :-)

Kyle
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
kgerbChief 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
 
alsamAuthor Commented:
Thanks a lot
0
 
kgerbChief EngineerCommented:
You're welcome:)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now