Hello everyone,
I have a multi-select combo box whereby I need to create an array from information that was clicked and create a sentence, in order, from the way the array was input.
Here's what I had after doing a bunch of testing last week:
Code:
Private Sub Command3_Click()
Dim text2 As String
Dim output2 As String
Dim item2 As Variant
'Fill a string with data which is semi-colon delimited; test by uncommenting one at a time.
'text2 = "Allison"
'text2 = "Allison;Brent"
'text2 = "Allison;Brent;Carl"
'text2 = "Allison;Brent;Carl;Darryl"
text2 = "Allison;Brent;Carl;Dan;Ed;Fred;Gail;Hank;Ichabad;Jack;Kermit"
'*****ADDITIONAL INFO HERE******
'Dim the array
Dim names2() As String '<--Dynamic Array (did not set an array boundary in the ()s
'*****ADDITIONAL INFO HERE******
'Split the text and put it into a dynamic array
names2() = Split(text2, ";")
output2 = Join(names2, ",")
'VBA for appropriate commas and 'and'
Dim result As String
For i = 0 To UBound(names2())
If UBound(names2()) = 0 Then
result = names2(0) & " is here alone."
ElseIf UBound(names2()) = 1 Then
result = names2(0) & " and " & names2(1) & " are here together."
Else
If i = UBound(names2()) Then
result = result & names2(i) & " were all here."
ElseIf i = (UBound(names2()) - 1) Then
result = result & names2(i) & ", and "
Else
result = result & names2(i) & ", "
End If
End If
Next
MsgBox result
End Sub
I understand how it's looping through the pre-provided string (
text2), but I need the ability to actually create that string, myself. Example, if I have the below combo box with the options selected, I want to be able to spit the information back out as:
..."went to the park, watched TV, took a shower, went to bed"...
I know how, from the VBA, above, to hand-jam that text string, change the delimiter, and get the desired output, but what I don't know is how to identify the boxes that are checked and to insert those with a -1 value into that dynamic array. I'm certainly not looking for anyone to do my work for me, but if anyone has a good link on how to create arrays based on the values entered in a multi-selectable combo box, I would certainly be thankful!
Thank you!