Update cells in two columns from different, but related lists based on input from one-CodePudding

On a hidden sheet, I have a list that contains some items, and a list that contains their bar-code numbers in the next column (15 items total). I have defined both of these as lists (Items and BCode). I already have some code that inserts into the active cell the items selected from the list of items.

Private Sub OK_Click()

MyVar = ""

For x = 0 To Me.Item.ListCount - 1
    If Me.Item.Selected(x) Then
        If MyVar = "" Then
            MyVar = Me.Item.List(x, 0)
        Else
            MyVar = MyVar & ", " & Me.Item.List(x, 0)
        End If
    End If
Next x

Me.Hide
ActiveCell.Value = MyVar

End Sub

This works great, but I would like to update another column on the same row with the string of bar-code numbers based on the selections made from the 'Item' list.

For Instance, if "Item1, Item3, Item6" were selected in the list, in addition to updating the current active cell in Column C, I'd like to add the list of barcodes for those items on the same row in Column K and display in the same format (separated by ", ") as in: "111111, 333333, 666666"

I'm open to formula suggestions too if there is an easier option there too, but I've been searching for a solution for about a week without being able to uncover whatever it is that I'm missing.

CodePudding user response：

Private Sub OK_Click()
dim MyVar as string
dim BarCodes as string
MyVar = ""

For x = 0 To Me.Item.ListCount - 1
    If Me.Item.Selected(x) Then
        If MyVar = "" Then
            MyVar = Me.Item.List(x, 0)
            BarCodes = Me.Item.List(x, 1)'Wherever the barcodes are
        Else
            MyVar = MyVar & ", " & Me.Item.List(x, 0)
            Barcodes = Barcodes & ", " & Me.Item.List(x, 1) 'Wherever the barcodes are
        End If
    End If
Next x

Me.Hide
ActiveCell.Value = MyVar
cells(ActiveCell.row,11).Value = Barcodes
End Sub