Inserting lines into a protected Excel spreadsheet

[vickycmha]

I have created a form in Excel which I need to protect in order to lock specific cells containing formulas. However, I also need to allow staff to insert additional lines as needed. Does anyone know a way to achieve both objectives?

 

[SkipVought]

Hi,

Not without code, because the FORUMLAS need to propogate to the new row.

Personally, I do not like to INSERT for the purpose of entering new data. That often messes up formulas that reference previous or following rows.

I'd opt for adding the data at the BOTTOM of the table and then SORTING into proper sequence, and adding the formulas via code, as new data is entered.

If you want to pursue a VBA solution, please repost in Forum707.

Skip,

_[red][/red]

 

[vickycmha]

Wow, quick response. The form is a mileage claim form, where entries have to be in date order. There can be multiple entries per day...totally unpredictable. It merely totals all entries and multiplies by the prescribed rate.
How do I do "code

 

[SkipVought]

it could be as simple as ....
[tt]
turn on your macro recorder

unprotect the sheet

copy the formula(s) in the last row

paste in the next row

protect the sheet

turn off the macro recorder
[/tt]
Do that. Post your question and code in Forum707, to find out how to make it all work.

Skip,

_[red][/red]

 

[LNBruno]

merely totals all entries and multiplies by the prescribed rate

Any chance you could put the formula at the TOP of a column and set the SUM to include a decent (maximum) number of rows?

e.g., Cell A1: =SUM(A3:A3000) * 0.035

< M!ke >
Acupuncture Development: a jab well done.

 

[Hasit]

However, I also need to allow staff to insert additional lines as needed.

<M!ke>'s suggestion is the one I would go with - its the easiest and simplest, and does not require any fanagling (technical term) on your part.

 

[vickycmha]

If I put the totals and formula at the top, I still wouldn't be able to lock those cells to prevent anyone from overwriting. They would still need access to add lines below into the appropriate date range.

 

[Rofeu]

you can set the cell property Locked to false in the data area and leave it True for the formulae. When the sheet is protected, unlocked cells can still be edited, but locked cells can not.

Cheers,

Roel

 

[vickycmha]

If I'm understanding you properly, I have already set the formulas/headings to "locked" and the deselected locked for the data area. However, when the sheet is protected, it won't allow the insertion of additional lines in the body of the form.

 

[LNBruno]

That was the idea behind including 1000s of rows in the formula; to preclude the need of users to insert. They could simply add to the bottom -- the next non-data row -- and the formula would still pick up the data.

The users could still enter data; just not insert rows, yes?





< M!ke >
Acupuncture Development: a jab well done.

 

[Rofeu]

whichever way you want it, when protecting the sheet, just check the option 'Insert Rows' et viola.

Cheers,

Roel

 

[vickycmha]

As to the # of rows to include, staff need to insert the additional rows in date order, and therefore have to insert in the middle of the document. These documents then have to be printed and submitted to supervisors for authorization. I wouldn't want the printed documents to be even more pages than necessary.


As for the "insert rows" option, the only way I know to insert rows is blocked when the document is protected. Where would I find the option to override this?

 

[SkipVought]

Add at BOTTOM.

SORT into order!

Skip,

_[red][/red]