Hi
I have a table of data which contains formulas in some columns and others are free text fields that users can input whatever they want. I wanted to protect the formulas from modification or accidental deletion so I can use the Protect Sheet function but leaving the free text field unlocked. Users need to be able to insert/create new rows into the bottom of that table with the same formulas. I've managed to do this by setting this table as a defined table so whenever a new row is inserted, the formulas are automatically copied into the new row. I was then able to create a macro to unlock the sheet, insert the new row and then protect the sheet again.
However, I now want to share the workbook so that multiple users can edit at the same time but it says tables are not compatible with shared workbooks and the table must be converted to a range instead first.
This is my current macro
How can I tweak the macro so that it does the above but no longer on a defined table?
Many thanks
I have a table of data which contains formulas in some columns and others are free text fields that users can input whatever they want. I wanted to protect the formulas from modification or accidental deletion so I can use the Protect Sheet function but leaving the free text field unlocked. Users need to be able to insert/create new rows into the bottom of that table with the same formulas. I've managed to do this by setting this table as a defined table so whenever a new row is inserted, the formulas are automatically copied into the new row. I was then able to create a macro to unlock the sheet, insert the new row and then protect the sheet again.
However, I now want to share the workbook so that multiple users can edit at the same time but it says tables are not compatible with shared workbooks and the table must be converted to a range instead first.
This is my current macro
VBA Code:
Sub InsertNewRow()
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table1")
Sheets("Sheet1").Unprotect
tbl.ListRows.Add
Sheets("Sheet1").Protect
End SubHow can I tweak the macro so that it does the above but no longer on a defined table?
Many thanks
