transfer data from userform by matching items in combobox with headers in sheet

[Omar M]

Hi
I have comboboxex (from1 : 7) under ACCOUNT NAME , and textboxes under DEBIT (from 1:7) , under credit (from 8:14)
and textbox15 under descriptions
here is form

the data in sheet

ACCOUNTS DAILY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	DATE	CONSTRAIN NO	DESCRIPTION	BALANCING CASE	TOTAL		BANK		SAFE		PURCHASE		SELLING		CAPITAL		PURCHASE RETURNING		SELLING RETURNING		RECEIVABLE		CREDITOR
2					DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT
3
4
5
6
7
8
sheet1

so when select item from any combobox should match with header in row1 and copy to sheet start from row 3 into debit or credit column under account name based on debit and credit in labels and account name in combobox fo form


example fill form

so should be in sheet

ACCOUNTS DAILY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	DATE	CONSTRAIN NO	DESCRIPTION	BALANCING CASE	TOTAL		BANK		SAFE		PURCHASE		SELLING		CAPITAL		PURCHASE RETURNING		SELLING RETURNING		RECEIVABLE		CREDITOR
2					DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT
3	11/08/2024	1	DEPOSIT CAPITAL	BANACED	2,000.00	2,000.00			2,000.00							2,000.00
sheet1

another example

should be in sheet

ACCOUNTS DAILY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	DATE	CONSTRAIN NO	DESCRIPTION	BALANCING CASE	TOTAL		BANK		SAFE		PURCHASE		SELLING		CAPITAL		PURCHASE RETURNING		SELLING RETURNING		RECEIVABLE		CREDITOR
2					DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT
3	11/08/2024	1	DEPOSIT CAPITAL	BANACED	2,000.00	2,000.00			2,000.00							2,000.00
4	11/08/2024	2	BUYING GOODS	BANACED	3,500.00	3,500.00		1,500.00		2,000.00	3,500.00
sheet1

another example

should be in sheet

ACCOUNTS DAILY.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	DATE	CONSTRAIN NO	DESCRIPTION	BALANCING CASE	TOTAL		BANK		SAFE		PURCHASE		SELLING		CAPITAL		PURCHASE RETURNING		SELLING RETURNING		RECEIVABLE		CREDITOR
2					DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT	DEBIT	CREDIT
3	11/08/2024	1	DEPOSIT CAPITAL	BANACED	2,000.00	2,000.00			2,000.00							2,000.00
4	11/08/2024	2	BUYING GOODS	BANACED	3,500.00	3,500.00		1,500.00		2,000.00	3,500.00
5	11/08/2024	3	SELLING GOODS	NOT BALANCED	10,000.00	15,000.00	10,000.00							15,000.00
sheet1

when copy data to sheet should fill date in column A and increment numbers 1,2,3.. in column B as column C will copy from textbox15
as TOTAL column in sheet will sum amounts for DEBIT column in the same row , also sum amounts for credit column in the same row
if the amounts are not the same amounts in column TOTAL for DEBIT,CREDIT then will show NOT BALANCED in column D , otherwise will be BALANCED.
every time I will insert new columns by add new accounts names inside the sheet.

thanks

 

[DanteAmor]

Try this:

Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim i As Long, lr As Long
  Dim f As Range
  Dim cmb As String
  Dim dbt As Double, cdt As Double
  Dim tbx1 As Variant, tbx2 As Variant
  
  Set sh1 = Sheets("Sheet1")
  lr = sh1.Range("E" & Rows.Count).End(3).Row + 1
  
  For i = 1 To 7
    cmb = Controls("ComboBox" & i).Value
    If cmb <> "" Then
      Set f = sh1.Range("1:1").Find(cmb, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        sh1.Range("A" & lr).Value = Date
        sh1.Range("B" & lr).Value = sh1.Range("B" & lr - 1).Value + 1
        sh1.Range("C" & lr).Value = TextBox15.Value
        
        tbx1 = Controls("TextBox" & i).Value
        tbx2 = Controls("TextBox" & i + 7).Value
        If tbx1 <> "" Then
          sh1.Cells(lr, f.Column).Value = CDbl(tbx1)
          dbt = dbt + CDbl(tbx1)
        End If

        If tbx2 <> "" Then
          sh1.Cells(lr, f.Column + 1).Value = CDbl(tbx2)
          cdt = cdt + CDbl(tbx2)
        End If
      End If
    End If
  Next

  If Not f Is Nothing Then
    sh1.Range("E" & lr).Value = dbt
    sh1.Range("F" & lr).Value = cdt
    sh1.Range("D" & lr).Value = IIf(dbt = cdt, "", "NOT ") & "BALANCED"
  End If

  For i = 1 To 7
    Controls("ComboBox" & i).Value = ""
    Controls("TextBox" & i).Value = ""
    Controls("TextBox" & i + 7).Value = ""
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Omar M]

Awesome !
can I fill comboboxexs for accounts names automatically instead of I add new accounts names manually ,please?
sorry I don't mentioned in OP .
thanks

 

[DanteAmor]

can I fill comboboxexs for accounts names automatically instead of I add new accounts names manually ,please?

Try this:

Private Sub CommandButton1_Click()
  Dim sh1 As Worksheet
  Dim i As Long, lr As Long
  Dim f As Range
  Dim cmb As String
  Dim dbt As Double, cdt As Double
  Dim tbx1 As Variant, tbx2 As Variant
  
  Set sh1 = Sheets("Sheet1")
  lr = sh1.Range("E" & Rows.Count).End(3).Row + 1
  
  For i = 1 To 7
    cmb = Controls("ComboBox" & i).Value
    If cmb <> "" Then
      Set f = sh1.Range("1:1").Find(cmb, , xlValues, xlWhole, , , False)
      If Not f Is Nothing Then
        sh1.Range("A" & lr).Value = Date
        sh1.Range("B" & lr).Value = sh1.Range("B" & lr - 1).Value + 1
        sh1.Range("C" & lr).Value = TextBox15.Value
        
        tbx1 = Controls("TextBox" & i).Value
        tbx2 = Controls("TextBox" & i + 7).Value
        If tbx1 <> "" Then
          sh1.Cells(lr, f.Column).Value = CDbl(tbx1)
          dbt = dbt + CDbl(tbx1)
        End If

        If tbx2 <> "" Then
          sh1.Cells(lr, f.Column + 1).Value = CDbl(tbx2)
          cdt = cdt + CDbl(tbx2)
        End If
      End If
    End If
  Next

  If Not f Is Nothing Then
    sh1.Range("E" & lr).Value = dbt
    sh1.Range("F" & lr).Value = cdt
    sh1.Range("D" & lr).Value = IIf(dbt = cdt, "", "NOT ") & "BALANCED"
  End If

  For i = 1 To 7
    Controls("ComboBox" & i).Value = ""
    Controls("TextBox" & i).Value = ""
    Controls("TextBox" & i + 7).Value = ""
  Next
End Sub

Private Sub UserForm_Activate()
  Dim i As Long, j As Long, k As Long
  Dim b() As Variant
  Dim rng As Range
  
  'Change "G" for the column where the names begin
  Set rng = Range(Cells(1, "G"), Cells(1, Cells(1, Columns.Count).End(1).Column))
  ReDim b(1 To WorksheetFunction.RoundUp(rng.Columns.Count / 2, 0))
  For j = rng.Cells(1, 1).Column To rng.Cells(1, rng.Columns.Count).Column Step 2
    k = k + 1
    b(k) = Cells(1, j)
  Next
  For i = 1 To 7
    Controls("ComboBox" & i).List = b
  Next
End Sub

 

[Omar M]

it's perfect
many thanks buddy.