How can i protect a calculated column in a table from being edited but allowing users to add rows?

TonyCrabb 

You can remove the Beep line and change Range("A1").Select to Range("E" & Target.Row).Select

TonyCrabb 

This may be a silly solution, but I wondered if--unless you're worried that people could intentionally or maliciously override your calculations--you couldn't just move the calculating column off to the right and hide it to prevent inadvertent entries. (Or, to be more extreme, put the calculations on a separate sheet, and hide the sheet).

TonyCrabb 

That is too difficult (we don't know how the user arrived there), but you can do something like this if you really want to irritate your users:

• Right-click the sheet tab.
• Select View Code from the context menu.
• Copy the code listed below into the worksheet module.
• Change Range("F:F") to the column you want to make inaccessible.
• If you ever want to edit the column, type the following in the Immediate window of the Visual Basic Editor and press Enter:
  
  Application.EnableEvents = False
  
  
  
• When you're done, do the same but with
  
  Application.EnableEvents = True
  
  
  
• Don't forget this, otherwise the worksheet won't react to events anymore.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("F:F"), Target) Is Nothing Then
        Application.EnableEvents = False
        Beep
        Range("A1").Select
        Application.EnableEvents = True
    End If
End Sub

TonyCrabb 

This version will try to insert a new row in the row of the active cell, so you need only one button to call the macro.

Check out the wsh.Protect part and change the True/False arguments as needed.

Sub AddRow()
    Dim wsh As Worksheet
    Dim tbl As ListObject
    Dim pwd As String
    Dim pos As Long
    Application.ScreenUpdating = False
    pwd = "secret"
    Set wsh = ActiveSheet
    wsh.Unprotect Password:=pwd
    Set tbl = ActiveSheet.ListObjects(1)
    pos = ActiveCell.Row - tbl.Range.Row
    tbl.ListRows.Add Position:=pos, AlwaysInsert:=False
    wsh.Protect Password:=pwd, DrawingObjects:=False, _
                Contents:=True, Scenarios:=False, _
                AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, AllowInsertingColumns:=True, _
                AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
                AllowDeletingColumns:=True, AllowDeletingRows:=True, _
                AllowSorting:=True, AllowFiltering:=True, _
                AllowUsingPivotTables:=True
    Application.ScreenUpdating = True
End Sub

TonyCrabb 

For example:

Sub AddRow()
    Dim strPassword As String
    strPassword = "secret"
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=strPassword
    ActiveSheet.ListObjects(1).ListRows.Add AlwaysInsert:=False
    ActiveSheet.Protect Password:=strPassword
    Application.ScreenUpdating = True
End Sub