CODEWITHSUNDEEP
excelvba
snt
snt

Reputation: 35

Assign a number to selected items in listbox

I'm writing a code that pulls part names from a spreadsheet and adds them to a listbox. The listbox will then allow the user to select multiple part names. Each part name in the spreadsheet has a corresponding numerical quantity 4 columns to the right of it (Part name in cell A1 has a corresponding number in cell E1). How can I assign the selected item in the listbox to its corresponding number in that same row? I have a code below, but I'm not sure if it's correct

Private Sub CommandButton1_Click()
ListBox1.List = Range("B7:B22").Value

'Private Function SelectedKits() As Integer
'Dim i As Integer
'For i = 0 To ListBox1.ListCount - 1
'For i = LBound(ListBox1.List) To UBound(ListBox1.List)
'If ListBox1.Selected(i) Then
'SelectedKits = ActiveCell.Offset(0, 4).Value

'End If
'Next i


End Sub

Upvotes: 0

Views: 171

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

You could have the listbox pull the number as well.

Give your listbox a ColumnCount of 4, adjust the ColumnWidths so all apart from the first column are 0 width.
enter image description here

Adjust your range to look at columns B:F.

Private Sub UserForm_Initialize()
    'Fully qualify your range othewise it will use whatever sheet is active.
    Me.ListBox1.List = ThisWorkbook.Worksheets("Sheet1").Range("B7:F9").Value
End Sub

Private Sub CommandButton1_Click()
    Dim x As Long
    Dim Msg As String
    
    For x = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(x) Then
            Msg = Msg & Me.ListBox1.List(x) & " - " & Me.ListBox1.List(x, 4) & vbCr
        End If
    Next x
    
    MsgBox Msg
End Sub

Upvotes: 1

Related Questions