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

 

[Micron]

I know this sort of thing in Access, not so much Excel. However, I can say that on any Access form I've ever created, the option buttons must belong to an option frame and the value you test for is passed to the frame (as in Frame0.Value or just Frame0), not the button value. So you add frame to form, then drag button onto it. If the frame does not highlight when you do this, you're doing it wrong (you can't copy/paste into a frame). I know you can add option buttons to a sheet without doing this, but you're using a userform so I'm thinking the frame is required. I answered even though I have not created a userform in a long time because you're still waiting, so I hope that helps.

 

[tubrak]

it doesn't make big difference about add frame . I use this way without any problem . my OP concentrate how treat the problems in my code .
thanks

 

[dmt32]

Hi,
try this update to your codes & see if any help

Private Sub ComboBox1_Change()
  Dim i As Long
  If Len(ComboBox1.Value) = 0 Then Exit Sub
  
  For i = 1 To 4
    If Me.Controls("OptionButton" & i).Value = True Then
        SumRange Me, Me.Controls("OptionButton" & i)
        Exit For
    End If
  Next i

End Sub

Sub SumRange(ByVal Form As Object, ByVal objOptionButton As Object)
    Dim Index           As Long
    Dim sh              As Worksheet
    Dim strName         As String
    Dim SumDebit        As Double, SumCredit As Double
    
    Set sh = ThisWorkbook.Worksheets("DATA")
    
    Index = Val(Right(objOptionButton.Name, 1))
    
    strName = Form.ComboBox1.Text
    
    SumDebit = Application.SumIf(sh.Columns(3), strName, sh.Columns(6))
    SumCredit = Application.SumIf(sh.Columns(3), strName, sh.Columns(7))
    
    'Debit              OPT Selected >     1      2  3     4
    Form.TextBox1.Value = Choose(Index, SumDebit, 0, 0, SumDebit)
    'Credit             OPT Selected >     1         2             3     4
    Form.TextBox2.Value = Choose(Index, SumCredit, SumCredit, SumCredit, 0)
    
     With Form.TextBox3
        .ForeColor = IIf(Val(.Value) > 0, rgbGreen, IIf(Val(.Value) < 0, rgbRed, rgbBlack))
     End With

End Sub

Private Sub OptionButton1_Click()
    'CASH
    SumRange Me, OptionButton1
End Sub

Private Sub OptionButton2_Click()
    'BANK
    SumRange Me, OptionButton2
End Sub

Private Sub OptionButton3_Click()
    'BANK & CASH
    SumRange Me, OptionButton3
End Sub

Private Sub OptionButton4_Click()
    'NOT PAID
    SumRange Me, OptionButton4
End Sub

Private Sub TextBox1_Change()
    'subtract DEBIT-CREDIT
    TextBox3.Value = Val(TextBox1.Value) - Val(TextBox2.Value)
End Sub

Private Sub TextBox2_Change()
    'subtract DEBIT-CREDIT
    TextBox3.Value = Val(TextBox1.Value) - Val(TextBox2.Value)
End Sub

Private Sub TextBox3_Change()
    'subtract DEBIT-CREDIT
    TextBox3.Value = Val(TextBox1.Value) - Val(TextBox2.Value)
End Sub

Read your post few times & not fully sure I have interpreted the required results for each option correctly but if code goes in right direction, you should be able to adjust as required. If the return values for each selection are not correct you change in the Choice selection part of code shown in bold here

     'Debit              OPT Selected >     1      2  3     4
    Form.TextBox1.Value = Choose(Index, SumDebit, 0, 0, SumDebit)
    'Credit             OPT Selected >     1         2             3     4
    Form.TextBox2.Value = Choose(Index, SumCredit, SumCredit, SumCredit, 0)

Hope helpful

Dave

 

[tubrak]

first thanks for your help , but actually it gives wrong values .

If the return values for each selection are not correct you change in the Choice selection part of code shown in bold here

change to what, forgive me can you be more specification,please?
this is what I got based on your code

correct should be

the correct summing based on mark by blue . the same thing with the rest of selections .

 

[dmt32]

Looks like need to apply an additional criteria using Sumifs for calculations

I am out most of day & will, if not resolved, will have a look when I return

Dave

 

[dmt32]

Untested but try replacing the SumRange code with updated version below & see if does what you want

Sub SumRange(ByVal Form As Object, ByVal objOptionButton As Object)
    Dim Index           As Long
    Dim sh              As Worksheet
    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 = Form.ComboBox1.Text
    
    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)
    
    
    
    '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]

thanks again the code just works for optionbutton(cash), optionbutton(bank) . the others shows 0 . it doesn't sum !

 

[dmt32]

thanks again the code just works for optionbutton(cash), optionbutton(bank) . the others shows 0 . it doesn't sum !

That suggests that the values In Columns C & E do not match the criteria in the Sumifs (you could have spaces)
You can test this by re-typing directly, the Name & Case values & then see if expected values are returned.

Dave

 

[tubrak]

That suggests that the values In Columns C & E do not match the criteria in the Sumifs (you could have spaces)

you're right . I fixed the space and works for option(not paid), but it doesn't work with BANK & CASH
BANK & CASH are not existed in column E then it will not sum as in option(not paid) .
it should search for BANK & CASH individually and sum together. when select optionbutton BANK & CASH .