Auto fill formulae when inserting rows

[Riker1074]

I have a protected worksheet that allows any user to insert new rows. Each
row has 2 formulas and 2 validation managed cells. When a user inserts a
row, the validation is copied, but the formulae are not. How can the
formulae be fromated to fill on inserted rows.

 

[Luke M]

When you go to insert, copy the row above where you want to insert, then
right click, paste special formulas.

 

[Riker1074]

Thanks Luke, but I didn't want to allow users to select locked cells.

Besides, the users of the file wouldn't know how to do what you suggested, I
really need a way to automate the process so it requires no action on the
part of the user. My target audience is office-illiterate

 

[KC Rippstein hotmail com>]

If you're using Excel 2003 or higher, Excel's "List" functionality (under the
Data menu) is great for inserting rows which retain the formulas and
formatting of the row directly above it without needing to copy & paste.

Just highlight your header row and data area and hit Ctrl+L to convert it to
an Excel List.

This function also converts the header row into an Auto Filter.

 

[Gord Dibben]

See David McRitchie's site for VBA method

http://www.mvps.org/dmcritchie/excel/insrtrow.htm


Gord Dibben MS Excel MVP

 

[Riker1074]

KC, thank you for your suggestion, I tried to use the list function, but the
attempt was thwarted by the locked cell status. Excel would not allow itself
to copy to a locked cell.

 

[Riker1074]

KC,

I also tried to use the list function paired with a validation. I created
the list off screen in a non-printing part of the sheet. I then targeted the
validation to each line of the list individually, unlocked the validated
cells and the list's cells, locked the worksheet and again excel stopped me.
This time it told me: You are attempting to move cells in a list. If I
focused on a cell in the list, no problem, except that the insertion wouldn't
add a row to the printable section of the sheet.

I know there must be a way to do this!

 

[Riker1074]

Gord,

I tried the macro, it requires that the protection allow users to insert
rows. Without that permission a dialog box pops up reading: Error: 400. Of
course I allowed insert row functionality and, when I hit the button, "Error:
400".

I'm ready to give up. MS Office is trying to kill me!

 

[KC Rippstein hotmail com>]

What if you just setup a macro button that behind the scenes turns off
protection, inserts a row in your list above where the cursor is located,
then turns protection back on? Since your password will be in the code,
you'll just want to apply a password to your VBA project as well so no one
can even see the code at all except you.

 

[Riker1074]

KC,
You are a genius! I can't believe I didn't think of that.