VBA with a button to add a row to a table and insert data

[goamnor]

Hello everyone,

I hope you are doing well. I am completly new to the VBA topic.

I was aked to do a VBA, that start when clicking a button. It should add a new row under the existing one in my case the start is row 24 and it should continue therafter. The popup should show the date of today and I should be able to add names to one column. The next column should show hand tipped notes about calls. All the data should be put in in the VBA and added to the last row below.

Has anyone an idea how to satrt or maybe has something similar with a code i can use.

Best

 

[igold]

Hi goamnor, welcome to the MrExcel Forum.

This would work better if you had a form to open and fill out the information that you want pasted to the table. That said this code uses two input boxes to get your information.
The code assumes that your button is named "CommandButton1", your active sheet is "Sheet1" and your table name is "Table1". To make this work as is, this code must be placed in the "Sheet1" (or whatever sheet you are using) module in the Visual Basic Editor.

Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim tbl As Object
    Dim nam As String, nte As String
    
    Set tbl = ws.ListObjects("Table1")
    ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
    nam = Application.InputBox("Input Name", , , , , , , 2)
    nte = Application.InputBox("Input Note for " & nam & " on " & Date, , , , , , , 2)
    tbl.DataBodyRange(tbl.ListRows.Count, 1) = Date
    tbl.DataBodyRange(tbl.ListRows.Count, 2) = nam
    tbl.DataBodyRange(tbl.ListRows.Count, 3) = nte
    
End Sub

Book1
	A	B	C	D	E
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23	DATE	NAME	NOTES
24	7/23/2024	JOHN SMITH	PLACED 3 ORDERS
25	7/24/2024	JANE DOE	30 DAYS PAST DUE
26	7/24/2024	BOB BARKER	WRONG PIRCE GIVEN
27
28
29
30
Sheet1

 

[goamnor]

Hello,

thank you for your support.

Is there any possibility to use something like a drop down menu for the names. The contact information will be above in roow 16-22. Would be great if I can just click on a name and it gets inserted.

Best

 

[igold]

If you are not using a form, then with an ActiveX Combo Box inserted on your worksheet, you would have to enter the range of names in the Properties Window of the Combo Box. After you insert the ComboBox on your worksheet, right click and select properties. Put your range in the highlighted row. Again, this code would have be in the worksheet module.

Then the following code would work

Private Sub ComboBox1_Click()
    
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim tbl As Object, cbo As Object
    Dim nam As String, nte As String

    Set cbo = Worksheets("Sheet1").ComboBox1
    Set tbl = ws.ListObjects("Table1")
    
    ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:=True
    nam = cbo.Value
    nte = Application.InputBox("Input Note for " & nam & " on " & Date, , , , , , , 2)
    tbl.DataBodyRange(tbl.ListRows.Count, 1) = Date
    tbl.DataBodyRange(tbl.ListRows.Count, 2) = nam
    tbl.DataBodyRange(tbl.ListRows.Count, 3) = nte

End Sub