Can a row containing protected & unprotected cells be copied?

[Jannine]

I have a large workbook that contains a ton of data, multiple formulas as
well as Vlookups which is distrbuted to a number of users. Currently the
workbook contains three tabs, two of which are data tabs supporting the look
up functions. One tab is considered the "data entry" page.

Some of the users are more Excel savvy than others therefore in an effort to
prevent users from accidentally deleting formulas we decided to lock cells
containing formulas and those populated by the result of a Vlookup. On the
data entry tab my users need to be able to copy & insert a row potentially in
the middle of a worksheet, with the ability to only change certain cells in
the new row. is this feasible? I can copy and paste individual cells that are
not locked however cannot copy a complete line.

 

[Luke M]

When you go to protect the sheet, place a check in the box to "insert rows".

By default, the new row assumes formatting now of row above, although you
can choose to use formatting from row below via the insert helper box (little
box that appears often when you paste, not sure of exact name)

 

[Jannine]

Luke - thanks for the input. I have the "insert rows" boxed checked however
I am unable to copy the entire row (above or below) into the new line...

Any thoughts?

 

[Luke M]

I'm afraid the method I described would only let you insert a "blank" new row
(although formatting would be the same).

To copy cells (presumably containing formulas) would in essence be letting a
user write formulas into protected cells, which of course goes against the
reason for protecting the worksheet.

 

[Bassman62]

Jannine,

The only way I can think of accomplishing your goal is to temporarily
Unprotect the worksheet.

Below is a macro that can be placed into a general module of the workbook.

As indicated, when run it will first ask for confirmation to insert a row.
If the answer is No, nothing will happen. If the answer is Yes then
unprotect the active worksheet, insert a blank row with the formatting of
the row above. Copy the contents of the row above and then re-protect the
worksheet.

It has no error trapping so if you pursue this method you may want to seek
more expert advice on macros.

Hope this helps.

Best Regards.



Private Sub InsertCopiedRow()
'
' UnProtect the ActiveWorksheet,
' At the active row, insert a row
' Copy from the row above
' Protect the Worksheet.
'
ActiveSheet.Unprotect
ActiveCell.EntireRow.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.EntireRow.FillDown
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

 

[Bassman62]

Sorry. Here is the complete macro.

Private Sub InsertCopiedRow()
'
' Display confirmation message box Yes/No
' UnProtect the ActiveWorksheet,
' At the active row, insert a row
' Copy from the row above
' Protect the Worksheet.
'
If MsgBox("Do you want to insert a row", vbYesNo + vbDefaultButton2) _
= vbYes Then
ActiveSheet.Unprotect
ActiveCell.EntireRow.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.EntireRow.FillDown
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Else
Exit Sub
End If

End Sub

 

[Jannine]

Thanks for the feedback. We found Bassman's suggestion to use a macro solves
the problem.

Again - I appreciate your help
Jannine

 

[Jannine]

Bassman - thanks bunches for the suggestion to use a macro, it seems to have
corrected the problem.

I appreciate your help!

Thansk again,
Jannine

 

[Bassman62]

You're welcome. Glad it helped.

Bassman - thanks bunches for the suggestion to use a macro, it seems to
have
corrected the problem.

I appreciate your help!

Thansk again,
Jannine