Add Rows Dynamically with a button in a row of data

[Src016]

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!!

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

<tbody>
</tbody>

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

Best,
Spencer

 

[JLGWhiz]

I have to tell you up front that I am not that sharp on tables, but you can try this.

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)

 

[Src016]

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).

'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

 

[JLGWhiz]

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.