Copy and Paste Locked Cells in a Protected Sheet

[gmconrad]

I have a sheet that is protected but does NOT have a password. The sheet contains some columns that are locked and others that are not. I would like to allow users of this sheet to insert new rows into the sheet. My problem is that I want some of the formulas that are locked to be available in the newly inserted rows.

For example, assume column A is unlocked and allows the user to enter free text. Column B is unlocked and the user should enter a quantity. Column C is unlocked and the user shoul enter a unit rate ($ per item). Column D is a locked formula that I would like to show up in the new row that multiplies column B times column C.

My sheet is more complicated than this with many columns...several of which have locked formulas...but it's the same basic concept. I can insert a new row BUT I can NOT get the correct formulas into these columns since the cells are locked.

I am a complete novice with code so please be specific if you suggest a macro. I've seen threads on other MB's that have suggested a macro that may turn off the protection to allow for stuff like this and then turn it back on when done???? I am using Excel 2007.

 

[engineer33]

brother unlock everything first by going to home format and unlock before u unlock highlight the whole spreadsheet once everything is unlock put the formulas once everything is done re lock the cells just remember if u have a locked cell u cannot do anything thats why there is this function

 

[gmconrad]

I do NOT want to have to manually unprotect the sheet to get these formulas to work. This sheet is going to be used by other people that have very basic Excel skills.

I would like them to basically be able to copy an entire row and 'insert copied cells" into a new row. I want them to be able to alter the unlocked cells but have the locked cells with formulas stay in their correct columns.

 

[murrayty]

When you lock the sheet click the select locked cells, select unlocked cells, insert rows and insert columns buttons.

That will allow them to insert rows/columns, change any unlocked cells and select locked cells to copy them or view formulas. Or is there some reason that doesn't give you everything you need?

 

[gmconrad]

thanks for the response. I have selected the options you mentioned when I protected the sheet. When I try to copy and insert a new row or copy a locked cell...I get the following message:

"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection..."

I think I see part of the problem now though. The area of the sheet where they need to insert rows has several entire columns protected. It appears the problem is that when the new row is inserted, it automatically has some of the cells locked. Is there was a way for newly inserted columns or rows to default that all cells are unlocked?

 

[murrayty]

You will have to set it up so that they are always inserting the rows below an unprotected row.

The way I have done it is to have a blank row that was unlocked and then a row beneath it that said insert new lines here. Other way is to only lock the cells above a certain point and let them add anything they want to the bottom of the sheet. You have to remember that if they insert lines(with locked parts or not) they are only able to change the unlocked parts and after they change it those parts will be unlocked forever unless you go and lock them later.

You have to remember what you are asking(you want to give people access to change a locked spreadsheet, kind of defeats the purpose), either you need to be smart about how you set the sheet up so that they can make necessary changes in a variety of unlocked cells or you are going to have to write a bunch of macros to handle every possible change you want the users to be able to make. Doing so is quite time consuming, I have spent weeks trying to get one of my sheets to do that...