Locking cells

[apar93]

I need to lock certain cells in a chart however I also need to be able to insert rows into this same chart. Is this possible.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Qty
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]Stapler
[/TD]
[TD]2
[/TD]
[TD]$50
[/TD]
[/TR]
</tbody>[/TABLE]

Take this chart as an example. I have locked the price cell as it contains a formula I do not want changed. I need to be able to copy the row (formulas included) and insert additional lines. It seems I cannot this when I lock the price cell. I hope this makes sense. Help please!

 

[apar93]

Hi Rick, I got this to work. Thank you for your help.
I have one more question. Is there a way to "hide" the formulas in the column that is being locked, so that no one else can see them?

 

[Rick Rothstein]

I have one more question. Is there a way to "hide" the formulas in the column that is being locked, so that no one else can see them?

Please clarify... are you asking if it is possible to display the results of the formulas in the cells in Column C but not let anyone see the underlying formulas themselves... or are you wanting to hide everything (formulas and results of those formulas) from the user?

 

[apar93]

I would like to hide the underlying formulas but not the result.

 

[Rick Rothstein]

I would like to hide the underlying formulas but not the result.

I cannot think of a way of hiding the formulas, but not the formula results, directly, but I can think of an indirect way. Insert a column next to Column C (that inserted column will become D). Put this formula in cell D1 and copy it down...

=C1

then hide Column C, apply the code I gave you earlier to Column D instead of Column C and finally, protect the worksheet against structural changes.

 

[apar93]

This is a great idea. Thanks!

 

[apar93]

Hi Rick,
I've stumbled on a problem while using your code... I cannot copy and insert rows, which is what I was trying to avoid! Thanks in advance.

 

[apar93]

Nevermind. I had another code doing that. You've been great. Thanks for all your help.

 

[apar93]

Actually. No I am right. This code does not let me copy and paste a row.... Ugh sorry!!