updating formulas at inserted rows with locked columns
 

I suspect that this is not such an unusual occurrence, and that there is a
work-around, but maybe not...

I have a worksheet in which it makes absolute sense (esp. knowing the end
users) to lock some of the columns.
The problem occurs when the user inserts a new row, which they will need to
do, and formulas from the rows above (this is within the field of data) need
to be copied into the new row. It's not possible -the cell / column is
locked.

Hopefully, I do not need to add a command button that assesses the cursor
position (selected row?), unlocks the worksheet, inserts the row, copies the
formula, then re-locks the row; however, this is what I am thinking will
need to be done.

as always, thanks in advance,
mark

updating formulas at inserted rows with locked columns
 

Hi Mark

As you do not want to enable the user to unprotect the sheet, it can only be
done as you described.

See if this will get you started:

Sub InsertLine()
ActiveSheet.Unprotect Password:="JustMe"
TargetRow = ActiveCell.Row
Rows(TargetRow).Offset(1, 0).Insert shift:=xlDown
Range("D" & TargetRow).Copy Range("D" & TargetRow + 1)
ActiveSheet.Protect Password:="JustMe"
End Sub

Regards,

Per

"Mark Kubicki" skrev i meddelelsen
...
I suspect that this is not such an unusual occurrence, and that there is a
work-around, but maybe not...

I have a worksheet in which it makes absolute sense (esp. knowing the end
users) to lock some of the columns.
The problem occurs when the user inserts a new row, which they will need
to do, and formulas from the rows above (this is within the field of data)
need to be copied into the new row. It's not possible -the cell / column
is locked.

Hopefully, I do not need to add a command button that assesses the cursor
position (selected row?), unlocks the worksheet, inserts the row, copies
the formula, then re-locks the row; however, this is what I am thinking
will need to be done.

as always, thanks in advance,
mark