Buttons to add lines

[Glio]

In the attached worksheet I want to be able to add new rows by clicking on the 'Add Line' buttons. When I click the button the first time it adds the new lrow at line 23, but each time after that it inserts a line at row 23, not at the bottom of the section.

The second Add Line button works in the same way. I have tried to write the VB so that the line number is incrimented for every click of the buttons, as you can see from the VB.

I know it's possible, but I am not yet capable!

AD263b.zip

Any help would appreciated.

Regards

Geoff Culbertson
Petersfield, Hampshire, UK

[Leith Ross]

Hello Glio,

The macros now allow for you delete lines, and still add new lines above the last formatted line. The macros have already been added to your buttons in the attached workbook.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Glio]

Leith,
Many thanks for your code. As you could see from my file I had been trying all sorts of ways to do what I wanted but without success. Your code works beautifully. I will be using it again in another worksheet and will try to augment it as well.

Thanks again

Geoff Culbertson
Petersfield, Hampshire, UK

[Leith Ross]

Hello Geoff,

Glad to hear it worked out for you. If you have any questions about what the code does, or need anything else, just ask.

Sincerely,
Leith Ross

[Glio]

Leith,
As you can see from the attached file, I have changed your code slightly on the Operation tab to clear the contents of the new line inserted, and I have used the same code on the PostEvent tab. All works well.

However on the Chronology tab I have tried to do the same thing and failed

On this tab the buttons work properly if you use any one button alone. This must be because I have hard-coded the row number for the start of each area.

I have tried to alter your code starting Set Rng and every time get the Message Box! As I don't really understand what this part of your code is doing (apart from working on the first tab!) I cannot make suitable changes on the second tab.

As you can see I have commented out the code which did not work and I hope you can see why I have changed some of it, especially for the General Observations & Observation lines which do not have merged cells in column B.

Hope you can help again!

Best regards

Geoff Culbertson
Petersfield, Hampshire, UK

[Leith Ross]

Hello Geoff,

Sorry to keep you waiting. I completely rewrote the macros. It should be easier and faster to do your coding now. All the code resides in two modules: a Standard Module and a Class Module. Once you add a new button, you only need to add it to the command button setup list module. The entry lines must be to the left of the button, and the line color must be the yellow that you used, which has a color index of 36. Everything has been added to the attached workbook.

CmdBtnClass Class Module
This customizes the ActiveX (Control Toolbox) added to the workbook to simplify adding lines to the worksheets. After adding a new Class Module to your project, rename it CmdBtnClass.

Please Login or Register  to view this content.

Setup Command Buttons
After you add a button to a worksheet you will need to add a reference to that button this module. This creates a new custom command button that will automatically add lines when clicked. The row of the first entry line is the row of the upper left corner that the button is in. This first row can be a header row. If it is you need to set the HasHeaderRow property to True. The Default is False. You will see examples of this in the module code.

Please Login or Register  to view this content.

This macro macro is also called by the Workbook_Open() and Workbook_BeforeSave() events. If for some reason the buttons stop working, run this macro.

Sincerely,
Leith Ross