Code to insert rows and lock command button

**fusion007** · 06-24-2011, 06:58 PM

Hi...Can anyone help with a code to insert a new row when a user starts to type in a cell. In the attached sample workbook when a user starts to type in cell A2 a new blank row should insert and the totals should include the new rows in the calculation. When the user starts to type in A3 then another new row should be inserted etc.

I also have a macro button which I would like to lock so when new rows are inserted or deleted it does not affect the macro button.

Thanks

**fusion007** · 06-27-2011, 04:47 AM

Hi can anyone please help with this? Thanks

**realniceguy5000** · 06-27-2011, 11:16 AM

See if this works for you?

.

**snb** · 06-27-2011, 12:04 PM

I think you cant't add rows: in E2003 a worksheet always consists of 65536 rows.
But you can change values in rows/cells.
Use as formulae: =SUM(D$1:D3) and =SUM(E$1:E3)

Then this suffices:

Please Login or Register  to view this content.

**fusion007** · 06-27-2011, 01:50 PM

Thanks Mike. The code works well but I will want the insert to be on always rather than the user have to click on the toggle button. How can the code be amended so that rows are inserted always once the user starts to input in the first row? Thanks

**snb** · 06-27-2011, 02:56 PM

You probably overlooked my suggestion...

**realniceguy5000** · 06-27-2011, 03:10 PM

No Problem,

Here is an update: Post back any issues...

**fusion007** · 06-27-2011, 03:56 PM

Hi snb I did try your code but it had no effect. Can you please check it and let me know? Thanks

**snb** · 06-27-2011, 05:17 PM

Enter something in A3; Enter

**fusion007** · 06-28-2011, 04:18 AM

Mike - your code works well and locks the macro button in place. At the moment the user has to either press enter or the down arrow in column A for a new row to be inserted before the totals row. Is there a way that as soon as a user starts to type something in a cell in column A a new row is inserted?

SNB - your code works as well but the formatting of the lines (borders) in columns D and E is copied down each time a new row is inserted.

Thanks

**snb** · 06-28-2011, 04:19 AM

You have my permission to adapt that in my code.

**fusion007** · 06-28-2011, 05:10 AM

Hi...I've had to add a few more columns to the workbook and have amended the code slightly but the borders are not picking up correctly and the totals are no being picked up from the new rows inserted. I'm attaching the workbook. Could you please cast your eyes over the code and let me know if there is anything I need to amend. Thanks

**snb** · 06-28-2011, 05:14 AM

Use my suggestion and replace 'clearcontents' by 'clear'.

**fusion007** · 06-28-2011, 05:47 AM

Thanks snb. I have added the code to my other part of the code that I use and it does not work. Here is the code

Please Login or Register  to view this content.

Any ideas?

**fusion007** · 06-29-2011, 08:02 AM

Hi Mike/snb any suggestions on this please? Thanks

**snb** · 06-29-2011, 08:11 AM

Remove the code you added; only keep mine.
For that purpose validation seems better suited.

**realniceguy5000** · 06-29-2011, 08:24 AM

Hi,

If you can get by with not inserting a row as soon as something is typed then this will work.

Please Login or Register  to view this content.

**fusion007** · 06-29-2011, 09:02 AM

Thanks Mike. When a new line is inserted the borders (lines above and beneath totals) are inserted in each new row from column A. Also the macro button exapands each time a row is inserted. I need to lock the macro button so it's fixed in one place even if new rows are inserted or deleted.

If a compromise needs to be made between locking the macro button and inserting new rows I'd rather be able to lock the macro button in place so when new rows are inserted manually the macro button does not change.

Thanks

**realniceguy5000** · 06-29-2011, 10:39 AM

Hi,

I suppose the reason your button is not working is because of setup

if you right click your button
Goto Properties
Click the Dont Move or Size with cells

It will solve your button issue moving.

For some reason you dont have that option just delete the text in the button then try the steps above, then add your text back in.

Here is an example workbook. Everything seems to be working on this test book?

Let me know if you need more help.

..

**fusion007** · 06-29-2011, 11:45 AM

Thanks again Mike. I tried your file and have the same issue as I was having. The border line should only appear in columns D and E but as soon as a new row is inserted the border line appears in all the columns from column A. Thanks

**realniceguy5000** · 06-29-2011, 12:44 PM

Hi, lets see if we can work that out...

This shouldl fix that issue..

Thanks,

**fusion007** · 06-30-2011, 10:12 AM

Hi Mike...didn't work for me. I'm attaching a copy of the workbook with the required number of coulmns and the code. Thanks

**realniceguy5000** · 06-30-2011, 02:06 PM

That extra data makes a differance, Try This:

**fusion007** · 07-01-2011, 06:10 AM

Hi Mike...thanks so much. That works great. If you get time can you please explain to me how the code works so I know for future? Thanks

**realniceguy5000** · 07-05-2011, 07:06 AM

Originally Posted by fusion007

Hi Mike...thanks so much. That works great. If you get time can you please explain to me how the code works so I know for future? Thanks

Hi, Please don't forget to mark your post solved if this solved your issues,

Here is a break down on what the script is doing...Just copy this script below and replace with the last script I sent you. I placed a stop command in the script so you can step through and watch what takes place at each line. You can press F8 to move from line to line.

Please Login or Register  to view this content.

**fusion007** · 07-05-2011, 10:00 AM

Thanks so much Mike. I will add reputation points for you. I will set this thread to solved.
I have another thread open on calculations, I'm stuck on the last part. I hope you will be able to help! Thanks

Code to insert rows and lock command button

LinkBack

Thread Tools

Rate This Thread

Display

Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Re: Code to insert rows and lock command button

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions