Hello,

I don't know if this is what I wanted, that when clicking on the listbox, the selected item fills the total textbox

I made a change to the following code (highlighted in red)

Private Sub InvoiceNumber2_Change()

Dim LR As Long
Dim r As Long
Dim i As Long
Me.WhichTest2.Clear
Me.TotalCost.Value = ""

With ActiveSheet
     LR = .Range("Q" & .Rows.Count).End(xlUp).Row
     For r = 2 To LR
         If .Cells(r, 17).Value = Val(InvoiceNumber2.Value) Then
            Me.WhichTest2.AddItem .Cells(r, 13).Value
            Me.WhichTest2.List(i, 1) = .Cells(r, 1).Value
            Me.WhichTest2.List(i, 2) = .Cells(r, 3).Row
             i = i + 1
     End If
     Next r
End With

End Sub

add the following code to the userform module

Private Sub WhichTest2_Click()

Dim i As Integer
Dim xChoise As String
Dim lastRow As Integer

i = 0

If Me.WhichTest2.ListCount = 0 Then
i = 0
Else
i = Me.WhichTest2.ListCount
End If

Debug.Print i
If i = 0 Then Exit Sub

xChoise = Me.WhichTest2.List(Me.WhichTest2.ListIndex, 0)
Debug.Print xChoise

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow
     If ActiveSheet.Cells(i, 13).Value = xChoise Then
         Me.TotalCost.Text = ActiveSheet.Cells(i, 23).Value
         Exit For
     End If
Next i

End Sub

Miguel,

Hi,

This is amazing thank you very much - however, it does not add up the values in column W if more than one item selected in the listbox WhichTest2.

Would you be able to help please?

Thank you.

Hello, 

I didn't quite understand, you want that, when entering the value in TextBox (InvoiceNumber2), 
the corresponding data appears in ListBox (WhichTest2), and when you click on a ListBox item (WhichTest2):

summarize all items found on the sheet with the same name selected in ListBox (WhichTest2) but column ("W"), and not all column ("W")values ? 

Private Sub WhichTest2_Click()

Dim i As Integer
Dim xChoise As String
Dim lastRow As Integer
Dim xCount As Double

i = 0

If Me.WhichTest2.ListCount = 0 Then
i = 0
Else
i = Me.WhichTest2.ListCount
End If

Debug.Print i
If i = 0 Then Exit Sub

xChoise = Me.WhichTest2.List(Me.WhichTest2.ListIndex, 0)
Debug.Print xChoise

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
xCount = 0

For i = 2 To lastRow
     If ActiveSheet.Cells(i, 13).Value = xChoise Then
         ' Me.TotalCost.Text = ActiveSheet.Cells(i, 23).Value
         If VBA.IsNumeric(ActiveSheet.Cells(i, 23).Value) = True Then
             xCount = xCount + ActiveSheet.Cells(i, 23).Value
         Else
             ' Cancel is true
         End If
     ' Exit For
     End If
Next i

Me.TotalCost.Text = xCount

End Sub

Miguel,

Hi,

Sorry if this is confusing.

I would like someone to enter the Invoice Number in to the InvoiceNumber2 textbox already put in to column Q. This will then show the tests which come under the Invoice Number in the multi-select listbox WhichTests2. 

If the user then selects more than one test in the WhichTests2 listbox, the value in TotalCost textbox will look up the InvoiceNumber2 in column Q, the tests selected in the WhichTests2 listbox in column M and add up the values in column W which relate to both the Invoice Number and the Tests selected.

I do not know if this is any clearer - I can add more values to my spreadsheet example and highlight this if it makes it easier?

I really appreciate your help on this.

Hello,

I think I understand

I made some changes

I added 4 columns to your listbox:

_ the first is the column data ("M") or 13
_ the second (new) is the column data ("W") or 23
_ the third is the column data ("A") or 1 - (comes from your code)
_ the fourth is the column data ("C") or 3 - (comes from your code)

only the first column of the listbox is visible, the other 3 are with .ColumnWidths = "0 pt"

add the following code to your userform:

Private Sub UserForm_Initialize()

With Me.WhichTest2
     .ColumnCount = 4
     .ColumnWidths = "100 pt;0 pt;0 pt;0 pt"
End With

End Sub

change the code you have for your textbox (InvoiceNumber2) by the following

Private Sub InvoiceNumber2_Change()

Dim LR As Long
Dim r As Long
Dim i As Long
Me.WhichTest2.Clear
Me.TotalCost.Value = ""

With ActiveSheet
LR = .Range("Q" & .Rows.Count).End(xlUp).Row
For r = 2 To LR
     If .Cells(r, 17).Value = Val(InvoiceNumber2.Value) Then
         WhichTest2.AddItem .Cells(r, 13).Value
         WhichTest2.List(i, 1) = .Cells(r, 23).Value
         WhichTest2.List(i, 2) = .Cells(r, 1).Value
         WhichTest2.List(i, 3) = .Cells(r, 3).Row
         i = i + 1
     End If
     Next r
End With

End Sub

remove the following code from your userform: WhichTest2_Click

lastly, add the following code to your userform

Private Sub WhichTest2_Change()

Dim i As Integer, x As Long
Dim lastRow As Integer
Dim xCount As Double

If Me.WhichTest2.ListCount = 0 Then Exit Sub

xCount = 0

For x = 0 To Me.WhichTest2.ListCount - 1
     If Me.WhichTest2.Selected(x) Then xCount = xCount + Me.WhichTest2.List(x, 1)
Next x

Me.TotalCost.Text = xCount

End Sub

Miguel,

Hi Miguel,

You are a genius! Thank you so much!! 😀