replace zero with balance by checkbox on userform and returning

[Abdo]

hello,
I have combobox1,checkbox1 and many sheets
and data in sheets

ABDO.xlsm
	A	B	C	D	E	F
1	ITEM	NAME	DEBIT	CREDIT	BALANCE	BAD DEBT
2	1	Abdo	2,000.00	1,000.00	1,000.00
3	2	Abbdo	5,000.00		5,000.00
4	3	Abdomen	3,000.00	4,000.00	-1,000.00
5	4	Addob	2,000.00	200.00	1,800.00
6	TOTAL		12,000.00	5,200.00	6,800.00
RC

ABDO.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	BAD DEBT
2	01/01/2025	INV 111	2,000.00		2,000.00
3	02/01/2025	INV 112		2,000.00	0.00
4	03/01/2025	INV 113		2,000.00	-2,000.00
5	04/01/2025	INV 114		1,000.00	-3,000.00
6	05/01/2025	PAID	5,000.00		2,000.00
7	06/01/2025	RECEIVED		500.00	1,500.00
8	TOTAL		7,000.00	5,500.00	1,500.00
AB

ABDO.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	BAD DEBT
2	01/01/2025	INV 114	2,000.00		2,000.00
3	01/01/2025	RECEIVED		1,000.00	1,000.00
4	02/01/2025	INV 115		2,000.00	-1,000.00
5	03/01/2025	INV 116		2,000.00	-3,000.00
6	TOTAL		2,000.00	5,000.00	-3,000.00
ADD

when select name from combobox1, checkbox1

then will search name in RC sheet in column B, show zero in BALANCE column E into cell for name and move amount to adjacent cell in column F like this

ABDO.xlsm
	A	B	C	D	E	F
1	ITEM	NAME	DEBIT	CREDIT	BALANCE	BAD DEBT
2	1	Abdo	2,000.00	1,000.00	0.00	1,000.00
3	2	Abbdo	5,000.00		5,000.00
4	3	Abdomen	3,000.00	4,000.00	-1,000.00
5	4	Addob	2,000.00	200.00	1,800.00
6	TOTAL		12,000.00	5,200.00	6,800.00
RC

if unselected checkbox1 then return amount in column E and delete amount from column F like this

ABDO.xlsm
	A	B	C	D	E	F
1	ITEM	NAME	DEBIT	CREDIT	BALANCE	BAD DEBT
2	1	Abdo	2,000.00	1,000.00	1,000.00
3	2	Abbdo	5,000.00		5,000.00
4	3	Abdomen	3,000.00	4,000.00	-1,000.00
5	4	Addob	2,000.00	200.00	1,800.00
6	TOTAL		12,000.00	5,200.00	6,800.00
RC

another example:
when select name from combobox1, checkbox1

then will search name based on sheet name , show zero in BALANCE column E into cell for name and move amount to adjacent cell in column F like this

ABDO.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	BAD DEBT
2	01/01/2025	INV 114	2,000.00		2,000.00
3	01/01/2025	RECEIVED		1,000.00	1,000.00
4	02/01/2025	INV 115		2,000.00	-1,000.00
5	03/01/2025	INV 116		2,000.00	-3,000.00
6	TOTAL		2,000.00	5,000.00	0.00	-3,000.00
ADD

if unselected checkbox1 then return amount in column E and delete amount from column F like this

ABDO.xlsm
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	BAD DEBT
2	01/01/2025	INV 114	2,000.00		2,000.00
3	01/01/2025	RECEIVED		1,000.00	1,000.00
4	02/01/2025	INV 115		2,000.00	-1,000.00
5	03/01/2025	INV 116		2,000.00	-3,000.00
6	TOTAL		2,000.00	5,000.00	-3,000.00
ADD

 

[DanteAmor]

Do the following:

1. Copy all the code and put it in your userform

Option Explicit
Dim initial As Boolean

Private Sub CheckBox1_Click()
  If initial = True Then Exit Sub
 
  If ComboBox1.ListIndex = -1 Or ComboBox1.Value = "" Then Exit Sub
 
  If ComboBox1.List(ComboBox1.ListIndex, 1) = "sheet" Then
    Call update_sheet(ComboBox1.Value, "A:A", "TOTAL")
  Else
    Call update_sheet("RC", "B:B", ComboBox1.Value)
  End If
End Sub

Private Sub ComboBox1_Change()
  Dim f As Range
 
  With ComboBox1
    If .ListIndex = -1 Or .Value = "" Then Exit Sub
    initial = True
    If .List(.ListIndex, 1) = "sheet" Then
      CheckBox1.Value = SetCheckBox(.Value, "A:A", "TOTAL")
    Else
      CheckBox1.Value = SetCheckBox("RC", "B:B", .Value)
    End If
    initial = False
  End With
End Sub

Sub update_sheet(sheetName As String, col As String, txt As String)
  Dim f As Range
 
  With Sheets(sheetName)
    Set f = .Range(col).Find(txt, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      If CheckBox1.Value = True Then
        .Range("F" & f.Row).Value = .Range("E" & f.Row).Value
        .Range("E" & f.Row).Value = 0
      Else
        .Range("E" & f.Row).Value = .Range("F" & f.Row).Value
        .Range("F" & f.Row).Value = ""
      End If
    End If
  End With
End Sub

Function SetCheckBox(sName As String, col As String, txt As String)
  Dim f As Range
    With Sheets(sName)
      Set f = .Range(col).Find(txt, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        If .Range("E" & f.Row).Value <> 0 And .Range("F" & f.Row).Value = "" Then
          SetCheckBox = False
        Else
          SetCheckBox = True
        End If
      End If
    End With
End Function

Private Sub UserForm_Initialize()
  Dim i As Long
  Dim sh As Worksheet, sh1 As Worksheet
 
  Set sh1 = Sheets("RC")
  With ComboBox1
    For i = 2 To sh1.Range("B" & Rows.Count).End(3).Row
      .AddItem sh1.Range("B" & i).Value
      .List(.ListCount - 1, 1) = "name"
    Next
 
    For Each sh In Sheets
      Select Case sh.Name
        Case sh1.Name, "Sheet1", "Summary", "etc"
       
        Case Else
          .AddItem sh.Name
          .List(.ListCount - 1, 1) = "sheet"
      End Select
    Next
  End With
End Sub

2. The code loads the combobox, so remove any code you use to load the combobox.

3. The code will add all the sheets in the workbook to the combobox, except the ones you put on this line:

Case sh1.Name, "Sheet1", "Summary", "etc"​

4. How it works.

Select the name; it can be a person's name or a sheet name. At this point, the code doesn't perform any updates; it will only update the checkbox value, activating or deactivating it depending on the sheet balance and what you selected in the combo box.​

Then, select or deselect the checkbox; the code will then update the sheet.​

 

[Abdo]

the code is awesome !

2. The code loads the combobox, so remove any code you use to load the combobox.

I don't want load all of sheets in the workbook just specific sheets (RC,AB,ADD) , may you adjust it,please?

 

[DanteAmor]

Change this part in the code:

Private Sub UserForm_Initialize()
  Dim i As Long
  Dim sh As Worksheet, sh1 As Worksheet
  
  Set sh1 = Sheets("RC")
  With ComboBox1
    For i = 2 To sh1.Range("B" & Rows.Count).End(3).Row
      .AddItem sh1.Range("B" & i).Value
      .List(.ListCount - 1, 1) = "name"
    Next
  
    For Each sh In Sheets
      Select Case sh.Name
        Case "AB", "ADD"
          .AddItem sh.Name
          .List(.ListCount - 1, 1) = "sheet"
      End Select
    Next
  End With
End Sub

 

[Abdo]

Great!

Thank you so much.