Insert a new row, keeping data validation - Macro/Button

[Tony Montana]

Hi all!

I am looking to add a button to a spreadsheet which will insert a new row at the top of the table (row 2), whilst keeping the data validation & formatting of the row below.

You will see in the example excel doc which I've attached that I have created an array alongside the table. This is done to prevent others from deleting rows, and does the job. I would like to keep this array (or any alternate which works with the "add row button") that will protect rows in the table from being deleted.

I would really appreciate some help ?

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Region	Date Entered	Company	Sector	Classification	Contact			1
2	East	10/08/2021	ABC Enterprise	Engineering	A	Bill			1
3	East	09/08/2021	IT Services	Tech	B	Roxanne			1
4	West	09/08/2021	Bulk Wholesale	Food	A	Jane			1
5									1
6									1
7									1
8									1
9									1
10									1
11									1
12									1
13									1
14									1
15									1
16									1
17									1
18									1
19									1
20									1
21									1
22									1
23									1
24									1
25									1
26
27
28
29
30
31
Sheet1

Cell Formulas
Range	Formula
I1:I25	I1	=1
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A2:A25	List	North, South, East, West
D2:D25	List	Manufacturing, Engineering, Tech, Food
E2:E25	List	A, B, C

 

[John_w]

I guess you are preventing rows in the table being deleted by locking cells I1:I25 and protecting the sheet.

A better approach is to use 'Allow Edit Ranges' on the table's data body range, which will prevent rows being deleted but allow the cells to be edited. First, run this macro once to set up the 'Allow Edit Ranges' on the table's data body range, lock its header row and protect the sheet.

Public Sub Lock_Table()

    Dim table As ListObject
    Dim editRange As AllowEditRange
   
    With ActiveSheet
   
        .Unprotect Password:="xxxx"
       
        'Unlock all cells on sheet
       
        .Cells.Locked = False
       
        Set table = .ListObjects(1)
       
        'Lock table's header row
       
        table.HeaderRowRange.Locked = True
       
        'Delete table's current edit range, named "TableData", if it exists
       
        For Each editRange In .Protection.AllowEditRanges
            If editRange.Title = "TableData" Then editRange.Delete
        Next
       
        'Add new edit range named "TableData" and assign it to table's data body
       
        .Protection.AllowEditRanges.Add Title:="TableData", Range:=table.DataBodyRange
       
        'Protect sheet with password.  UserInterfaceOnly:=True means other VBA procedures don't need to Unprotect the sheet to insert rows or change cells etc.
       
        .Protect UserInterfaceOnly:=True, Password:="xxxx", DrawingObjects:=False, Contents:=True, Scenarios:=True
   
    End With
   
End Sub

Now you can run the following macro to insert a new top row in the table:

Public Sub Insert_Top_Row()

    Dim table As ListObject
   
    With ActiveSheet
        '.Unprotect  'not needed because sheet was protected with UserInterfaceOnly:=True in Lock_Table macro
        Set table = .ListObjects(1)
        table.DataBodyRange.Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    End With

End Sub

 

[Tony Montana]

This is incredible, all works and am very happy.

Thank you John

 

[John_w]

I think you'll find that the Insert_Row macro doesn't work after you close and reopen the workbook without running the Lock_Table macro again. Lock_Table is only meant to be run once to set up the protection on the table.

The problem is that .Protect UserInterfaceOnly:=True doesn't persist in the saved workbook and must be 'redone' when the workbook is opened. Fix this by adding this code to the ThisWorkbook module:

Private Sub Workbook_Open()    
    ThisWorkbook.Worksheets("Sheet1").Protect UserInterfaceOnly:=True, Password:="xxxx", DrawingObjects:=False, Contents:=True, Scenarios:=True
End Sub

where "Sheet1" is the name of the sheet containing the table and "xxxx" is the password used to protect that sheet.