Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating For RowCount to accommodate inserted rows | Excel Programming | |||
Update References in Formulas when Columns are Inserted and Rows are Skipped | Excel Programming | |||
Automatically copy formulas/functions to inserted rows. | Excel Worksheet Functions | |||
Can array formulas work if rows are inserted? | Excel Worksheet Functions | |||
How to have formulas and formats auto copy to new inserted rows | Excel Discussion (Misc queries) |