Insert two columns by select item from combobox on userform

[Omar M]

Hi experts,
I would insert two columns for new account name with the same formatting and borders.
here is original data in sheet before add item in combobox

AC
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
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	BALANCED	2000	2000			2000							2000
4	11/08/2024	2	BUYING GOODS	BALANCED	3500	3500		1500		2000	3500
5	11/08/2024	3	SELLING GOODS	NOT BALANCED	10000	15000	10000							15000
6	11/08/2024	4	SELLING GOODS	BALANCED	3000	3000			3000					3000
7	11/08/2024	5	INCREASE CAPITAL	BALANCED	15000	15000			15000							15000
8	12/08/2024	6	DECREASE CAPITAL	BALANCED	3000	3000				3000					3000
Sheet1

add new account name from combobox1

the result will be in columns Y,Z

AC
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	DATE	CONSTRAIN NO	DESCRIPTION	BALANCING CASE	TOTAL		BANK		SAFE		PURCHASE		SELLING		CAPITAL		PURCHASE RETURNING		SELLING RETURNING		RECEIVABLE		CREDITOR		EXPENSES
2					DEBIT	CREDIT	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	BALANCED	2000	2000			2000							2000
4	11/08/2024	2	BUYING GOODS	BALANCED	3500	3500		1500		2000	3500
5	11/08/2024	3	SELLING GOODS	NOT BALANCED	10000	15000	10000							15000
6	11/08/2024	4	SELLING GOODS	BALANCED	3000	3000			3000					3000
7	11/08/2024	5	INCREASE CAPITAL	BALANCED	15000	15000			15000							15000
8	12/08/2024	6	DECREASE CAPITAL	BALANCED	3000	3000				3000					3000
Sheet1

but if I select the same account to try adding again and it's already existed , then should shows message" the account name is already existed, you can't repeat it" , and don't add it.
every time add new account name as long I need it.
thanks

 

[DanteAmor]

Try this:

Private Sub CommandButton1_Click()
  Dim f As Range
  Dim lc As Long
  
  With ComboBox1
    If .Value = "" Then
      MsgBox "Enter Account name"
      Exit Sub
    End If
    
    Set f = Range("1:1").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "The account name is already existed"
    Else
      lc = Cells(2, Columns.Count).End(1).Column + 1
      Cells(1, lc).Value = .Value
      Cells(2, lc).Value = "DEBIT"
      Cells(2, lc + 1).Value = "CREDIT"
      Range("E1:F2").Copy
      Cells(1, lc).PasteSpecial xlPasteFormats
    End If
  End With
End Sub

 

[Omar M]

Perfect !
just I want fixing borders from row 3 , it doesn't make borders from row3 until last row contains date in column A.
may you do it ,please?

 

[DanteAmor]

it doesn't make borders from row3 until last row contains date in column A.
may you do it ,please?

Try:

Private Sub CommandButton1_Click()
  Dim f As Range
  Dim lc As Long
  
  Application.ScreenUpdating = False
  With ComboBox1
    If .Value = "" Then
      MsgBox "Enter Account name"
      Exit Sub
    End If
    
    Set f = Range("1:1").Find(.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      MsgBox "The account name is already existed"
    Else
      lc = Cells(2, Columns.Count).End(1).Column + 1
      Cells(1, lc).Value = .Value
      Cells(2, lc).Value = "DEBIT"
      Cells(2, lc + 1).Value = "CREDIT"
      Range("E1:F2").Copy
      Cells(1, lc).PasteSpecial xlPasteFormats
      With Cells(3, lc).Resize(Range("A" & Rows.Count).End(3).Row - 2, 2).Borders
        .LineStyle = xlContinuous
        .Color = Range("A1").Borders.Color
      End With
    End If
  End With
  Application.ScreenUpdating = True
End Sub

 

[Omar M]

Really Great !
thank you so much.