VBA script help needed to unlock a sheet to add a new line then protect again

[Vansar]

Hello there,

I have a 47 (AA - AU) column table which has 8 Formulae which i need to protect from overwriting, however the table needs to have the ability to add new rows on a daily basis,
I have used data validation to protect the cells, but much to my amusement you can still copy and paste over the Validation, i have looked at protecting the sheet but then i cannot add rows??

Is there a VBA Script to create a button to Un-protect the sheet, add new row then apply the protection again, it would need to be password protected

The columns which need protecting are L,Q,R,AA,AI,AJ,AK,AL

Any help or assistance would be greatly appreciated

Many thanks

Craig

 

[DanteAmor]

Hello there,

I have a 47 (AA - AU) column table which has 8 Formulae which i need to protect from overwriting, however the table needs to have the ability to add new rows on a daily basis,
I have used data validation to protect the cells, but much to my amusement you can still copy and paste over the Validation, i have looked at protecting the sheet but then i cannot add rows??

Is there a VBA Script to create a button to Un-protect the sheet, add new row then apply the protection again, it would need to be password protected

The columns which need protecting are L,Q,R,AA,AI,AJ,AK,AL

Any help or assistance would be greatly appreciated

Many thanks

Craig

Put the following macro in a button, it works in the following way:
- Select a cell.
- Press the button.
- The macro unprotects, inserts a row above the selected row, protects the sheet again.

Sub Macro1()
    ActiveSheet.Unprotect "abc"
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveSheet.Protect "abc", 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
End Sub