Change Total value in sheet based on deletion selected item from listbox on userform

[Maklil]

Hi Guys
first thank for mr.Dante Amore for theses codes and I modfied what I need it, but I need still some fixing for two codes if anybody can help and has free time I would appreciate it.
first in DETAILS sheet contains multiple range . every range contains multiple rows and TOTAL row based on first code doesn't show the TOTAL row is relating of data in sheet when select items from combobox1: 3
what I want also show TOTAL row is relating separated range when search on userform by combobox1:3
second code when delete specific row ,then automatically change the value in TOTAL row for column E by decrease the previous value which has ever been existed (for instance let's take range A1:E6 when delete row5 then the value in TOTAL row for column E becomes (100+100)=200 , not (200+100+100)=400 should calculate after second row for each range .and if I select range A14:E17 and delete row 16 , then the value in TOTAL row for column E becomes 400 as second row .

add.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	200
3	1/2/2023	CSS-100	INV-A123	ITTT-100/AS-1	100
4	1/2/2023	CSS-100	INV-A123	ITTT-100/AS-1	100
5	5/1/2023	CSS-100	INV-A123	ITTT-100/AS-1	200
6	TOTAL				400
7
8	DATE	CUSTOMER	INV NO	ITEM	QTY
9	11/12/2022	CSS-100	INV-A123	ITTT-100/AS-2	300
10	1/2/2023	CSS-100	INV-A123	ITTT-100/AS-2	100
11	5/1/2023	CSS-100	INV-A123	ITTT-100/AS-2	100
12	TOTAL				200
13
14	DATE	CUSTOMER	INV NO	ITEM	QTY
15	11/13/2022	CSS-102	INV-A125	ITTT-100/AS-2	400
16	1/2/2023	CSS-102	INV-A125	ITTT-100/AS-2	300
17	TOTAL				300
DETAILS

userform

Option Explicit


Dim a As Variant




Private Sub ComboBox3_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long
  
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    If ComboBox1 = "" Then txt1 = a(i, 2) Else txt1 = ComboBox1
    If ComboBox2 = "" Then txt2 = a(i, 3) Else txt2 = ComboBox2
    If ComboBox3 = "" Then txt3 = a(i, 4) Else txt3 = ComboBox3
    If LCase(a(i, 2)) Like LCase(txt1) & "*" And _
       LCase(a(i, 3)) Like LCase(txt2) & "*" And _
       LCase(a(i, 4)) Like LCase(txt3) & "*" Then
      k = k + 1
      For j = 1 To 5
        b(k, j) = a(i, j)
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub

Private Sub UserForm_Activate()
  a = Sheets("DETAILS").Range("A2:E" & Sheets("DETAILS").Range("B" & Rows.Count).End(3).Row).Value
End Sub
Private Sub ComboBox1_Change()
  
     Call FilterData
End Sub

Private Sub ComboBox2_Change()
   
     Call FilterData
End Sub

Private Sub ButtonDeleteRow_Click()
  Dim i As Integer
  Application.ScreenUpdating = False
  
  If MsgBox("Are you sure you want to delete this data row?", vbYesNo + vbQuestion, "Delete Row?") = vbYes Then
    Worksheets("DETAILS").Select
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
      If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Delete
      End If
    Next i
  End If
  Application.ScreenUpdating = True
End Sub

 

[Maklil]

for more details
when search by comboboxex should show TOTAL last row like this

when select item from listbox

and when delete from list box should be

and the same thing in sheet

add.xlsm
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2022	CSS-100	INV-A123	ITTT-100/AS-1	200
3	11/2/2023	CSS-100	INV-A123	ITTT-100/AS-1	100
4	1/2/2023	CSS-100	INV-A123	ITTT-100/AS-1	100
5	TOTAL				200
DETAILS

another example

when delete

add.xlsm
	A	B	C	D	E
13	DATE	CUSTOMER	INV NO	ITEM	QTY
14	11/13/2022	CSS-102	INV-A125	ITTT-100/AS-2	400
15	TOTAL				400
DETAILS

 

[Maklil]

 

[Maklil]

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: fixing two things when try delete item from listbox on userform & change TOTAL
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.