You are here:

Excel/Concatenate values in listbox?

Advertisement


Question
QUESTION: Hi Tom,

I'm trying to add TWO combo box values to a list box as ONE concatenated value (i.e. cboPlayers_D1 / cboPlayers_D2).

I tried the following without success:
lstRegistered_D.AddItem (Me.cboPlayers_D1&" / "&Me.cboPlayers_D2)

Can you tell me what I may be doing wrong?

Thank you very much!

Joe

ANSWER: Joe,

If that is copied out of your code, then you need to have spaces around the &


lstRegistered_D.AddItem (Me.cboPlayers_D1 & " / " & Me.cboPlayers_D2)

I created a userform with your objects and tested it:

Private Sub CommandButton1_Click()
lstRegistered_D.AddItem (Me.cboPlayers_D1 & " / " & Me.cboPlayers_D2)
End Sub

Private Sub UserForm_Initialize()
Me.cboPlayers_D1.List = Application.Transpose(Array("A", "B", "C", "D", "E"))
Me.cboPlayers_D2.List =  _
  Application.Transpose(Array("1", "10", "100", "1000", "10000", "100000"))

End Sub


That worked fine for me in Excel 2010.  

if you still have problems try

lstRegistered_D.AddItem Me.cboPlayers_D1.Text & " / " & Me.cboPlayers_D2.Text

in any case, the parentheses around the concatenation are not needed.

--
Regards,
Tom Ogilvy
 




---------- FOLLOW-UP ----------

QUESTION: Thanks, Tom!  That did the trick.

Now, when I'm ready to move these two names (Black, Jack / Smith, Bill) from the list box to a worksheet, I want them to read (Jack Black / Bill Smith) on the worksheet.

I used this code to transpose one name elsewhere in my project, but I'm not sure how to modify to transpose the other name like the example above:

[CODE]
Dim i As Long, r As Range, v1 As Variant, s As String
Set r = ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
For i = 0 To lstRegistered_S.ListCount - 1
 v1 = Split(lstRegistered_S.List(i), ", ")
 r.Offset(i, 0) = Application.Trim(v1(UBound(v1))) & " " & Application.Trim(v1(LBound(v1)))
'  r.Offset(i, 1) = Application.Trim(v1(LBound(v1)))
Next i
[CODE]

I'm thinking I need another variant, but I'm not sure how to do this.
Your help is always much appreciated!

ANSWER: Joe,

this worked for me (I changed the listbox name to end in D so adjust as needed. )

Private Sub CommandButton1_Click()
lstRegistered_D.AddItem (Me.cboPlayers_D1.Text & " / " & Me.cboPlayers_D2.Text)
End Sub

Private Sub CommandButton2_Click()
Dim i As Long, r As Range, v1 As Variant, s As String, ws As Worksheet
Dim v1A As Variant, v1B As Variant
Set ws = ActiveSheet
Set r = ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
For i = 0 To lstRegistered_D.ListCount - 1
v1 = Split(lstRegistered_D.List(i), " / ")
v1A = Split(v1(LBound(v1)), ", ")
v1B = Split(v1(UBound(v1)), ", ")
s = Application.Trim(v1A(UBound(v1A))) & " " & _
    Application.Trim(v1A(LBound(v1A))) & " / " & _
    Application.Trim(v1B(UBound(v1B))) & " " & _
    Application.Trim(v1B(LBound(v1B)))
r.Offset(i, 0) = s
' r.Offset(i, 1) = Application.Trim(v1(LBound(v1)))
Next i
End Sub

Private Sub UserForm_Initialize()
cboPlayers_D1.List = Application.Transpose(Array("Black, Jack", "Smith, John", "Mouse, Minnie", "CoolJ, LL"))
cboPlayers_D2.List = Application.Transpose(Array("Booth, John", "Lincoln, Abraham", "Snooks, Mel", "Turk, Madge"))

End Sub



---------- FOLLOW-UP ----------

QUESTION: Okay, so I added two more variants (v1C & v1D) and I'm trying to return the 2nd and 3rd values from the split array.  I've tried a few things, but I'm not quite understanding how to do this.  I keep getting the 1st or 4th value???  

Where am I failing here?

[CODE]
Set r = ws.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
For i = 0 To lstRegistered_F.ListCount - 1
 v1 = Split(lstRegistered_F.List(i), " / ")
 v1A = Split(v1(LBound(v1)), ", ") 'returns 1st value
 v1B = Split(v1(LBound(v1, 2)), ", ") '???? - Trying to return 2nd value
 v1C = Split(v1(LBound(v1, 3)), ", ") '???? - Trying to return 3rd value
 v1C = Split(v1(UBound(v1)), ", ") 'returns 4th value
 s2 = Application.Trim(v1A(UBound(v1A))) & " " & Application.Trim(v1A(LBound(v1A))) & " / " & _
      Application.Trim(v1B(UBound(v1B))) & " " & Application.Trim(v1B(LBound(v1B))) & " / " & _
      Application.Trim(v1C(UBound(v1C))) & " " & Application.Trim(v1C(LBound(v1C))) & " / " & _
      Application.Trim(v1D(UBound(v1D))) & " " & Application.Trim(v1D(LBound(v1D)))
 r.Offset(i, 0) = s2
Next i
[CODE]

Thanks again for your help!  You are always so accurate and fast to respond -- greatly appreciated!!

Joe

Answer
Joe,

So if I understand the situation, the List contains entries with 3 right slashes like


Smith, Joe / Schempf, Brad / Mouse, Minnie / Duck, Donald

if that is the case than v1 will contain

v1(lbound(v1)) = "Smith, Joe"
v1(lbound(v1) + 1) = "Schempf, Brad"
v1(lbound(v1) + 2) = "Mouse, Minnie"
v1(Ubound(v1)) = "Duck, Donald"

so you would do

v1 = Split(lstRegistered_F.List(i), " / ")
v1A = Split(v1(LBound(v1)), ", ") 'returns 1st value
v1B = Split(v1(LBound(v1) + 1), ", ")
v1C = Split(v1(LBound(v1) + 2), ", ")
v1C = Split(v1(UBound(v1)), ", ") 'returns 4th value

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.