CODEWITHSUNDEEP
vbaexcel
Variax
Variax

Reputation: 1057

Protect excel sheet but allow table to be modified

I have a spreadsheet locked so that the user cannot modify the cell values, but so I can run VBA macros to do calculations. My code goes like this:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect "Password", UserInterfaceOnly:=True
Next
Other_function()
End Sub

This works fine, preventing the user from modifying cells, but allowing this example to work:

Private Sub CommandButton1_Click()
    Worksheets(1).Range("A20").Value = "AAA"
End Sub

However, when I try to add rows to a table, I get a 1004 error:

Dim ws As Worksheet
Dim tbl As ListObject
Set ws = Worksheets("SecondSheet")
Set tbl = ws.ListObjects("TableName")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add
With newrow
    .Range(1) = onevalue
    .Range(2) = twovalue
    .Range(3) = threevalue
    ...
End With

How can I tell Excel that I need to put new rows in this table without unprotecting the whole thing every time?

Upvotes: 2

Views: 2413

Answers (2)

Teasel
Teasel

Reputation: 1340

You cannot accept only VBA to do it, you have to unprotect the sheet before doing your operations and reprotecting it after.

You can allow adding/removing columns in a protected sheet with AllowInsertingColumns or AllowDeletingColumns but if you do so, you will accept the script AND the user to insert or remove columns.

Source for AllowInsertingColumns and AllowDeletingColumns.

Upvotes: 1

jkpieterse
jkpieterse

Reputation: 2986

Unfortunately, protecting a worksheet disallows adding or deleting rows to/from a table. You'll have to unprotect the sheet.

Upvotes: 0

Related Questions