VBA to unlock worksheet to allow table row to be added and then protected again

paulbiddis · Feb 16th 2022

Good evening all,

Sorry for the lengthy subject but not easy to explain.

I have a table in an excel worksheet. I want to lock a number of columns in the table (but not all) and then protect the worksheet to ensure that formulas in those columns are not accidentally deleted/changed. Unfortunately, once I protect the worksheet I am no longer able to add rows to the table via entering a new invoice number in the first column.

In the attached example a new invoice number in column A will, when unprotected/unlocked, will automatically add a new line across the table column range. The locked columns are H and J.

Any help would be appreciated - there is no password.

Many thanks.

Paul

**royUK** · Feb 17th 2022

Try this

Code

Sub Mymacro()
    '
    Dim oTbl As ListObject
    Dim oNewRow As ListRow
    Const sPw As String = "Secret"
    
    With Sheets("Travel")
        Set oTbl = .ListObjects(1)
        .Unprotect sPw
        Set oNewRow = oTbl.ListRows.Add
        .Protect sPw
    End With
End Sub

Display More

paulbiddis · Feb 17th 2022

Hi Roy,

Thanks for that. It works... but I didn't explain that I wanted the vba to run when a new invoice number is entered into first empty cell in column A (i.e. the first cell in column A below the table).

I have put in a change event in the worksheet code that will run your code but there are 2 issues:

1. The change event looks for any change to any cell in column A. I just want it to run when the first empty celling that column is changed.I cannot find a way to include the vba to find the first empty cell within the change event code.

2. When I run the change event the code adds approx. 150 rows instead of just one. This does not happen when I run your code separately.

I have attached the revised worksheet.

Thanks.

Paul

PS. I have removed the password.

**royUK** · Feb 17th 2022

How can you add a new number if the Sheet is protected. You need an empty row to work with.

This code needs an empty row in the Table for the user to type in. When the code runs it adds a new row for the next time it is used.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oTbl As ListObject
    Dim oNewRow As ListRow
    
    Set oTbl = ListObjects(1)
    On Error GoTo err_exit
    If Intersect(Target, oTbl.ListColumns(1).Range) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    
err_exit:
    Unprotect
    Set oNewRow = oTbl.ListRows.Add
    Protect
    
    Application.EnableEvents = True
End Sub

Display More

paulbiddis · Feb 17th 2022

Hi Roy,

Thanks. Getting there. The code does what it says on the tin

Just a couple of questions:

1. Not all columns in the worksheet. Column A is one of these. Hence the original ask.

2. Is it possible to limit the change event to the last empty cell in column A? (I have found that any further change to already entered entries in column A results in another row being added when it is not necessary.

Thanks.

Paul

**royUK** · Feb 18th 2022

Your protection is not really much use, because it is set wrong. You need the cells in the table to be Locked or else they can be changed even if the sheet is protected. Read this.
I'm not sure what you mean about the columns.
When you talk about the last cell, you are using a Table so the last cell needs to be in the Table not the sheet.

Try this

Code

Sub Mymacro()
    '
    Dim oTbl As ListObject
    Dim oNewRow As ListRow
    
    With Sheets("Travel")
        Set oTbl = .ListObjects(1)
        
        If Intersect(Target, oTbl.DataBodyRange(oTbl.ListRows.Count, 1)) Is Nothing Then Exit Sub
        .Unprotect
        Set oNewRow = oTbl.ListRows.Add
        .Protect
    End With
End Sub

Display More

VBA to unlock worksheet to allow table row to be added and then protected again

Participate now!

Share