code summing into textboxes based on combobox,optionbuttons doesn't give right values

[tubrak]

hi
I would populate total of textbox1 (DEBIT) and textbox2 (CREDIT) and shows the value into textbox3 (NET) after subtract DEBIT-CREDIT
so if I select the name from combobox1 then should populate TOTAL values into textbox1,2 which is relating the name , if I select the name from combobox1 and select option button (CASH) then should sum the values just the credit in textbox2 based on column G, if if I select the name from combobox1 and select option button (BANK) then should sum the values just the credit in textbox2 based column G ,if I select the name from combobox1 and select option button (BANK,CASH) then should sum the values together just the credit in textbox2 based on column G ,if I select the name from combobox1 and select option button (not paid) then should sum the values just the debit in textbox3 based on column F .
note: the combobox1 depends on column C and the optionbuttons depends on column E , the textbox1 (debit) based on column F ,the textbox2 (CREDIT ) based on column G .
I try collecting some ideas from the internet and this is what I got but unfortunately it gives wrong values by summing all of values in columns F,G with ignore selected optionbutton,combobox . so I hope from the experts fix this problem.

Private Sub ComboBox1_Change()
   Dim sh As Worksheet, f As Range
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then Exit Sub
  Set sh = Sheets("DATA")
  Set f = sh.Range("C:C").Find(ComboBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
  TextBox1.Value = Application.Sum(Worksheets("DATA").Range("f:f"))
  TextBox2.Value = Application.Sum(Worksheets("DATA").Range("g:g"))
  End If
 
End Sub

Private Sub OptionButton1_Click()
If OptionButton1.Value = True And ComboBox1.Value <> "" Then
Set f = sh.Range("C:C").Find(ComboBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
TextBox2.Value = Application.Sum(Worksheets("DATA").Range("g:g"))
End If
End If
End Sub

Private Sub OptionButton2_Click()
If OptionButton2.Value = True And ComboBox1.Value <> "" Then
Set f = sh.Range("C:C").Find(ComboBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
TextBox2.Value = Application.Sum(Worksheets("DATA").Range("g:g"))
End If
End If
End Sub

Private Sub OptionButton3_Click()
If OptionButton3.Value = True And ComboBox1.Value <> "" Then
Set f = sh.Range("C:C").Find(ComboBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
TextBox2.Value = Application.Sum(Worksheets("DATA").Range("g:g"))
End If
End If
End Sub

Private Sub OptionButton4_Click()
If OptionButton4.Value = True And ComboBox1.Value <> "" Then
Set f = sh.Range("C:C").Find(ComboBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
TextBox2.Value = Application.Sum(Worksheets("DATA").Range("f:f"))
End If
End If
End Sub

Private Sub TextBox2_Change()
TextBox3.Value = Val(TextBox1.Value) - Val(TextBox2.Value)
End Sub

this is the data in sheet

cs1.xlsm
	A	B	C	D	E	F	G
1	ITEM	DATE	NAME	INVOICE NO	CASE	DEBIT	CREDIT
2	1	01/01/2021	TUBRIKO	AS-12	NOT PAID	2000	-
3	2	02/01/2021	TUBRIKO	AS-12	CASH	-	100.00
4	3	05/03/2021	TUBRIKO	AS-12	BANK	-	1,500.00
5	4	05/05/2021	TUBRIKO	AS-13	CASH	1000	500.00
6	5	05/05/2021	TRIBOKO	AS-14	BANK	2000	1,500.00
7	6	02/02/2021	TRIBOKO	AS-15	BANK	3000	3,500.00
8	7	04/02/2021	TRIBOKO	AS-14	BANK	-	500.00
9	8	04/05/2021	TRIBOKO	AS-15	BANK	-	1,000.00
10	9	04/04/2021	TARKONO	AS-16	NOT PAID	2500	-
11	10	04/04/2021	TARKONO	AS-17	NOT PAID	1000	-
12	11	04/04/2021	TARKONO	AS-16	CASH		500.00
data

and the userform

I hope somebody help

 

[dmt32]

try this update

Sub SumRange(ByVal Form As Object, ByVal objOptionButton As Object)
    Dim Index           As Long
    Dim sh              As Worksheet
    Dim i               As Long
    Dim strName         As String, strCase As String
    Dim SumDebit        As Double, SumCredit As Double
    
    Set sh = ThisWorkbook.Worksheets("DATA")
    
    Index = Val(Right(objOptionButton.Name, 1))
    
    strName = Trim(Form.ComboBox1.Text)
    
    If Index = 3 Then
        For i = 1 To 2
            strCase = Choose(i, "CASH", "BANK")
            
            SumDebit = SumDebit + Application.SumIfs(sh.Columns(6), sh.Columns(3), strName, sh.Columns(5), strCase)
            SumCredit = SumCredit + Application.SumIfs(sh.Columns(7), sh.Columns(3), strName, sh.Columns(5), strCase)
        Next i
        
    Else
        strCase = Choose(Index, "CASH", "BANK", "BANK & CASH", "NOT PAID")
        
        SumDebit = Application.SumIfs(sh.Columns(6), sh.Columns(3), strName, sh.Columns(5), strCase)
        SumCredit = Application.SumIfs(sh.Columns(7), sh.Columns(3), strName, sh.Columns(5), strCase)
    End If
    
    
    'Debit
    Form.TextBox1.Value = SumDebit
    'Credit
    Form.TextBox2.Value = SumCredit
    
    With Form.TextBox3
        .ForeColor = IIf(Val(.Value) > 0, rgbGreen, IIf(Val(.Value) < 0, rgbRed, rgbBlack))
    End With
    
End Sub

Dave

 

[tubrak]

magnificent ! all things are great!
many thanks for your time and assistance

 

[dmt32]

magnificent ! all things are great!
many thanks for your time and assistance

Glad resolved & appreciate the feedback

Dave