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