Adding new cells to sheet protection

[jethro4640]

Good Afternoon,

BLUF: Is it possible to add new cells to a sheets' "protection/lock" without supplying the original password?...

I was tasked with making a "logbook" document that would act much like pen and paper.
I've gotten a macro to add a new line to the top of the worksheet (keeping the most recent entry on top so the user doesn't have to scroll down hundreds of rows to add a new entry). That same macro then adds a date/time stamp to the new "A1" cell and auto selects A2 cell for the user to type.

Now they want it to be protected from alterations right after the row is finished. I've added the protection selection for rows 2 and beyond in the macro but just realized that when the macro does it, it doesn't put in a password.

Then i tried to have locked cells unselect-able, but unlocked cells selectable and format-able. Then i would have had the macro add a new row (1), select the now-row (2) and then go into format-cells and put a check in the "lock" box. my hope was that it would have then added that worksheet's already password protected rights to that new row. what i found out is that if there is ANY cell protected on that worksheet the "protection tab" in "format cell" is removed for other cells. Any ideas?

below is the code that did what i'm looking for the most - added a row, locked the previous row - but didn't have the required password protection.

Code:
Sub Macro15()'
' Macro15 Macro
'


'
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False
ActiveSheet.Unprotect
Range("A2:H38").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowInsertingRows:=True, AllowSorting:= _
True, AllowFiltering:=True
Range("b1").Select
End Sub

 

[Fishboy]

Good Afternoon,

BLUF: Is it possible to add new cells to a sheets' "protection/lock" without supplying the original password?...

I was tasked with making a "logbook" document that would act much like pen and paper.
I've gotten a macro to add a new line to the top of the worksheet (keeping the most recent entry on top so the user doesn't have to scroll down hundreds of rows to add a new entry). That same macro then adds a date/time stamp to the new "A1" cell and auto selects A2 cell for the user to type.

Now they want it to be protected from alterations right after the row is finished. I've added the protection selection for rows 2 and beyond in the macro but just realized that when the macro does it, it doesn't put in a password.

Then i tried to have locked cells unselect-able, but unlocked cells selectable and format-able. Then i would have had the macro add a new row (1), select the now-row (2) and then go into format-cells and put a check in the "lock" box. my hope was that it would have then added that worksheet's already password protected rights to that new row. what i found out is that if there is ANY cell protected on that worksheet the "protection tab" in "format cell" is removed for other cells. Any ideas?

below is the code that did what i'm looking for the most - added a row, locked the previous row - but didn't have the required password protection.

Hi jethro4640, welcome to the boards.

This assumes that you actually know the password for the protection, but the following code would unprotect the sheet, add a new row at row 1, put in the time / date stamp, lock all the used cells but still allowing you to type in the new row, then reprotect the sheet using the password again:

Sub AddProtection()
    ' Unprotect sheet with password
    ActiveSheet.Unprotect "Password"
        ' Turn off cell lock for all cells
        Cells.Locked = False
        ' Insert new row at row 1
        Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ' Update the new A1 with the time / date stamp
        Range("A1").Value = Now()
        ' Work out the last row of data based on column A
        LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        ' Work out the last column of data based on the last row
        LastCol = ActiveSheet.Cells(LastRow, Columns.Count).End(xlToLeft).Column
        ' Lock cell A1
        Range("A1").Locked = True
        ' Lock from A2 to the last column of the last row
        Range("A2", Cells(LastRow, LastCol)).Locked = True
        ' Select cell B1
        Range("B1").Select
    ' Re-protect sheet with password
    ActiveSheet.Protect "Password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowInsertingRows:=True, AllowSorting:= _
        True, AllowFiltering:=True
End Sub