Log In
Register
Home
November 16, 2020
Offline
Hi,
Thank you for your help on the last subject I posted about - it is much appreciated!
I now have a new problem with another tab on my userform.
I have a userform which has a textbox (InvoiceNumber2), a listbox (WhichTest2) and another textbox (TotalCost).
I would like the user to be able to type the invoice number in to InvoiceNumber2 (found in column Q). Based on this value it fills the listbox WhichTest2 (found in column M) - I already have the code for this bit.
Dim LR As Long
Dim r As Long
Dim i As Long
WhichTest2.Clear
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, 1).Value
WhichTest2.List(i, 2) = .Cells(r, 3).Row
i = i + 1
End If
Next r
End With
Then, based on the value in InvoiceNumber2 and the tests selected from WhichTest2, it adds up the values in Column W and puts this value in to textbox TotalCost.
For example: -
InvoiceNumber2 = 12345 (Column Q, found in rows 3-5)
WhichTest2 = Test1 and Test 2 and Test 3 (Column M, found in rows 3-5)
TotalCost = SUM(Column W, rows 3-5)
I have tried to change the code below with no success: -
Dim LR As Long
Dim r As Long
Dim i As Long
TotalCost.Clear
With ActiveSheet
LR = .Range("W" & .Rows.Count).End(xlUp).Row
For r = 2 To LR
If .Cells(r, 1).Value = Val(InvoiceNumber2.Value) Then
TotalCost.AddItem .Cells(r, 23).Value
TotalCost.List(i, 1) = .Cells(r, 1).Value
TotalCost.List(i, 2) = .Cells(r, 3).Row
i = i + 1
End If
Next r
End With
I have also tried to change the below code:-
LR = .Range("Q" & .Rows.Count).End(xlUp).Row
If WhichTests.Selected(i) = True Then
TotalCost.Value =
End If
End If
I have searched a lot of forums to try and change/ learn code to make this work but to no prevail! (No idea if this is possible).
See AnswerFebruary 20, 2020
Offline
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,
November 16, 2020
Offline
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.
February 20, 2020
Offline
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,
November 16, 2020
Offline
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.
February 20, 2020
Offline
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,
Answers Post
1 Guest(s)
