Listbox Hidden Values

[OldManDemosthenes]

I have a multiselect listbox which is populated from "InputData" column D. The "Run" button on my userform must preform 2 tasks.

First is just make a list a of the selection(s) as they appear in column D. This list appears on sheet 1 column A.

Second, I would like to create a string that appears on sheet 1 B1 of all the selected values (selection 1, selection 2, etc..). However, I want the string values to come from "InputData" column E. For example, column D has Chicago, IL but the value in the same row in column E is just Chicago.

So "Run" would theoretically result with sheet 1: A1 as "Chicago, IL", A2 "New York, NY", A3 "San Francisco, CA"; and B1 as "Chicago, New York, San Francisco"

Here is my code so far. Currently I have created the string of results from the values in "InputData" column D not column E

Private Sub cbRun_Click()

Dim i As Long, cty As String​

With lbCity​

.List(i) = ""​

For i = 0 To .ListCount - 1​

If .Selected(i) Then​

cty = cty & .List(i) & ", "​

End If​

Next i​

End With​

Cells(1, 2) = cty​

Unload Me​

End Sub

Private Sub UserForm_Initialize()

Dim rngCity As Range​

With lbCity​

.RowSource = ""​

For Each rngCity In Worksheets("InputData").Range("D2:D" & _​

Worksheets("InputData").Range("D65536").End(xlUp).Row)​

.AddItem rngCity.Value​

Next rngCity​

End With​

End Sub

 

[Norie]

So do you have a hidden column?

If you don't why not add one and populate it when you populate the first column?

Option Explicit

Private Sub cbRun_Click()
Dim i As Long, cty As String

    With lbCity
      
        For i = 0 To .ListCount - 1

            If .Selected(i) Then

                cty = cty & .List(i) & ", " & .List(i, 1)

            End If

        Next i

    End With

    Cells(1, 2) = cty

    Unload Me

End Sub
 
Private Sub UserForm_Initialize()
Dim rngCity As Range

    With lbCity

        .RowSource = ""

        .ColumnCount = 2

        .ColumnWidths = .Width & ";0"
        
        For Each rngCity In Worksheets("InputData").Range("D2:D" & _
                                                          Worksheets("InputData").Range("D65536").End(xlUp).Row)

            .AddItem rngCity.Value

            .List(.ListCount - 1, 1) = rngCity.Offset(, 1)

        Next rngCity

    End With

End Sub

 

[OldManDemosthenes]

Thanks for the quick response

The code now creates a string that says "Chicago, IL, ChicagoNew York, NY, New YorkSan Francisco, CA, San Francisco"

Any ideas? This is my first week of using VBA code so my troubleshooting ability is still lacking

 

[Norie]

So it's just the city which is in column E?

Just remove List(i) from the concatenation - that's returing the value from the 1st column, List(i,1) returns the 2nd column.

You could actually do it without 2 columns in the listbox, but I thought you wanted 2 columns.

If you don't then post back, but try what I suggested first.

 

[OldManDemosthenes]

That works!

Appreciate the help