Macro adds rows with values, not blanks

Hello!

Using the modified Dave McRitchie InsertRowsAndFillFormulas macro as well as a highlight active row macro. Rows are being added, but they have values in them. Workbook attached. Anyone see how to fix this code?

Thanks!

Lost

Please Login or Register  to view this content.

Hi Learning,

I see you have a single worksheet in this workbook. You also have 3 userforms. There is about 400 lines of code. You have event code behine the worksheet that fires each time a change happens on it.

When I tried to open this workbook I got a debug stop message on the Unprotect line.

My suggestion is to take the Subs that are behind the worksheet that are NOT event macros and put them into a MODULE. I see the code in the Sub you mention goes through all sheets. I'm not sure that is allowed when in code behind a single worksheet.

Read http://www.cpearson.com/excel/Events.aspx for more background

hi, leaning, try to comment out the following line or check attachment:

Please Login or Register  to view this content.

in Sub InsertRowsAndFillFormulas()

MarvinP,

I cleaned up the attached workbook. Thanks for the help!

Watersev: Your help fixed the problem!

Two last things:

1) The "data table" itself needs to be unlocked to allow user edit. And the cells below it need to be locked. If the user adds rows, those rows need to have just the A and B cells unlocked.

2) Sometimes when the user adds rows, the formatting (borders, text alignment, don't make it to the new row. So, the user has to unprotect the document, do edits, and then reprotect. (If you look at the attached, I added a row under the "17", and the borders didn't carry over.)

Any ideas about these?

Lost

All,

In case anyone needs this thread, here's what the attached workbook has:

1) Table-like section with 2 columns for user to add data. (This section has nothing below it.)
2) Button to add new blank row to the bottom of that section.
3) New row code allows changing the properties (border, font, data validation, etc.) for each of the individual cells added (Columns A:G for this).
4) Column G has a SUM formula (via VBA) and is locked.
5) There is a named range (WONum) that looks at Col A of the section (without listing the blanks), so you can use those values for data validation in other places.
6) There is code to highlight each row as you are working on it.
7) The Delete Rows button can delete multiple rows selected, not just one.

Thanks to everyone for your help on this!

Regards,

Lost