Add Rows Dynamically with a button in a row of data

Src016

New Member
Joined
May 21, 2019
Messages
10
Hi All,

I would like to build a sheet that allows users to highlight a row, click a button, and insert a new row of data after that row ( and consequently shift the data below the added row down). The sub I currently have just adds the new data to the bottom of the existing data, but I want the ability to add a row anywhere in the data based on a selected location. For example - I want the user to be able to select row 20:20, press the add row button, have that new row of data inserted as 21:21 while shifting the data below down one row. Any ideas? This would be a huge help!!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Checkbook_AddRow()


'Declare local variables------
Dim lo As ListObject
Dim ws As Worksheet
Dim wsSettings As Worksheet
'-----------------------------


'Set worksheet and list object variables--------------
Set wsSettings = ThisWorkbook.Worksheets("Settings")
Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
'-----------------------------------------------------


ws.Unprotect wsSettings.Range("Settings_Password")


MsgBox "Please highlight the Row you want to insert a line of data below"


'Add new row to table and set all table fields
lo.ListRows.Add (lo.ListRows.Count + 1)
lo.DataBodyRange(lo.ListRows.Count, 1) = "9999"
lo.DataBodyRange(lo.ListRows.Count, 2) = "PENDING"
lo.DataBodyRange(lo.ListRows.Count, 3) = "0"
lo.DataBodyRange(lo.ListRows.Count, 4) = wsSettings.Range("Settings_FYStartDate")
lo.DataBodyRange(lo.ListRows.Count, 5) = "0.001"
lo.DataBodyRange(lo.ListRows.Count, 6) = "11111.00"
lo.DataBodyRange(lo.ListRows.Count, 7) = "first name"
lo.DataBodyRange(lo.ListRows.Count, 8) = "last name"
lo.DataBodyRange(lo.ListRows.Count, 9) = "9999999"
lo.DataBodyRange(lo.ListRows.Count, 10) = wsSettings.Range("Settings_FYStartDate")
lo.DataBodyRange(lo.ListRows.Count, 11) = "Enter notes here………………"
lo.DataBodyRange(lo.ListRows.Count, 12) = lo.DataBodyRange(1, 12)


ws.Protect wsSettings.Range("Settings_Password"), AllowFiltering:=True


End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks again for all your help! This site never ceases to amaze me!

Best,
Spencer
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I have to tell you up front that I am not that sharp on tables, but you can try this.

Code:
Dim lo As ListObject, rg As Range, r As Long
Dim ws As Worksheet
Dim wsSettings As Worksheet
'Set worksheet and list object variables--------------
Set wsSettings = ThisWorkbook.Worksheets(1)
Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
Set rg = Application.InputBox("Please click in the row on which you want to insert a line of data beneath", Type:=8)
rg.Offset(1).EntireRow.Insert
r = rg.Row
lo.DataBodyRange(r, 1) = "9999"
lo.DataBodyRange(r, 2) = "PENDING"
lo.DataBodyRange(r, 3) = "0"
lo.DataBodyRange(r, 4) = wsSettings.Range("Settings_FYStartDate")
lo.DataBodyRange(r, 5) = "0.001"
lo.DataBodyRange(r, 6) = "11111.00"
lo.DataBodyRange(r, 7) = "first name"
lo.DataBodyRange(r, 8) = "last name"
lo.DataBodyRange(r, 9) = "9999999"
lo.DataBodyRange(r, 10) = wsSettings.Range("Settings_FYStartDate")
lo.DataBodyRange(r, 11) = "Enter notes here………………"
lo.DataBodyRange(r, 12) = lo.DataBodyRange(1, 12)
 
Last edited:
Upvote 0
Thank you for the input! Most of it worked, but I think I found code that works for the user in the below code. I am having issues entering the correct information into the newly generated row; see the "set all table fields" section highlighted in green. Ideally all those values (i.e. 'PENDING', '99999', etc.) would be entered into the correct cell of the newly generated row. Any ideas? I just need to reference the active cell and tell vba to insert the appropriate data in columns 2 through 12 of the active row (i.e. the row just created).

Code:
'Declare local variables------
Dim lo          As ListObject
Dim ws          As Worksheet
Dim wsSettings  As Worksheet
Dim topLine     As Integer: topLine = 17


'-----------------------------


'Set worksheet and list object variables--------------
Set wsSettings = ThisWorkbook.Worksheets("Settings")
Set ws = ActiveSheet
Set lo = ws.ListObjects(1)
'-----------------------------------------------------


ws.Unprotect wsSettings.Range("Settings_Password")


' only this section is new
ws.Rows(IIf(ActiveCell.Row > topLine, _
        ActiveCell.Row, _
        topLine)) _
        .EntireRow.Insert


' set all table fields
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.ActiveCell, 1) = "9999"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.ActiveCell, 2) = "PENDING"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 3) = "0"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 4) = wsSettings.Range("Settings_FYStartDate")[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 5) = "0.001"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 6) = "11111.00"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 7) = "first name"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 8) = "last name"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 9) = "9999999"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 10) = wsSettings.Range("Settings_FYStartDate")[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 11) = "Enter notes here………………"[/COLOR]
[COLOR=#008000]lo.DataBodyRange(lo.ListRows.Count, 12) = lo.DataBodyRange(1, 12)[/COLOR]


ws.Protect wsSettings.Range("Settings_Password"), AllowFiltering:=True


End Sub
 
Upvote 0
Like I said, tables are not my expertise. I am still learning how the rows and columns work with VBA. The old VBA references for standard sheets columns and rows does not work with tables' columns and rows. Offset also requires care when using in a table. The code suggested in my post was compiled from trial and error, not from general knowledge.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top