Adding & Deleting Rows in Worksheet while maintaining Formulas

Good evening. I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.

I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:

1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table

The code I'm using for my "Add" button is:

Please Login or Register  to view this content.

The code I'm using for my "Delete" button is:

Please Login or Register  to view this content.

The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above). Any thoughts or suggestions would be appreciated.

Matthew

Yes, you were correct in your assumption about the formulas. I included your code in to the form and added two lines of code to automatically unprotect a protect the worksheet automatically and BINGO....problem solved. Thanks a lot.

Matthew

Oops...I spoke to soon. I'm getting a run-time error 1004 and the debugger is pointing me to the following line of code. I'm thinking that I deleted one line too many (or something) that now is causing the problem. Thoughts?

Please Login or Register  to view this content.

Thanks.

Matthew

Fixed my own problem...silly mistake on my part. I had commented out the unprotect and protect lines of code. It's all good now. Thanks.

Matthew

p24leclerc,

If you're still game to problem solve, I ran into a few hiccups that I've been trying to work around and I can't seem to lock onto the problem or should I say solution.

1) I'm trying to prevent the user from deleting the first row (row 3). Once the first row is gone then the form is useless.
2) I'm trying to get the totals (along the last row) and the averages (in column F) to be blank unless numbers are entered into columns C and E.

I've been successful in getting a msg box to appear (with instructions for the user) but that's about it. I've got to find a way to make this "dummy proof" which is why I'm putting the effort into such a basic form. Thanks.

Matthew

That change worked wonders. Thanks a bunch for your help. I've attached the final version of the form here. I included a calendar feature to the form that I received from jaslake (John) on this forum. It worked like a charm. I also added some code that allows text wrap in merged cells and few other tweaks. Thanks again.

Matthew