Button Function to Produce New Rows

[Bargyy]

G’day,

My first post, here goes – I’m fairly new to learning VBA so any help at all on the following would be greatly appreciated.

I was curious how it’s possible to create a spreadsheet which has two functions…curious as I hoped to use a spreadsheet with the function:

A button at the top of the spreadsheet which adds a new sequential number to the next available row (I can do this part, but am struggling with the following part)…I would then like the numbered sequence to be inserted from a specific location i.e. set the first number to Column C, Row 10.
Then, each time the button is clicked, it will insert a new row (i.e. Column C, Row 11). As the new row is inserted, the cells will all be populated with a defined set of data i.e. a check box which triggers a count of adding 13, and a count formula for another cell.

Any help to support pulling the spreadsheet together would be greatly appreciated.

[humdingaling]

an example file would help clarify exactly what you are after

To Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Bargyy]

Thanks for your reply - I've attached the spreadsheet example here.

Tab 1 is the before; tab 2 demonstrating the after.

Thank you for any help you're happy to provide.Test Function.xlsx

[Richard Buttrey]

The approach I generally adopt for stuff like this is to keep a master row of formulae/constants above my data - in the example attached on row 2 which you can of course hide. I've named A2:F6 'NewRow' and created a simple macro

Please Login or Register  to view this content.

[humdingaling]

your numbering system is a bit of a curve ball to work around

Please Login or Register  to view this content.

[Richard Buttrey]

...sorry I forgot to attach the file I used. This contains the code I gave in post #4 and it includes the newrow range name.

Notice how I've used cell formatting to to show the end ')' character. This means the cell value is a numeric and not text - as it would be if it showed '1)'

[Bargyy]

Hi Richard/ humdingaling,

Thank you both for you're help; I'm afraid Richard I can't get your macro to function, an error seems to display.

Humdingaling, thank you I'm able to run the macro and it works perfectly. The only issue is I can't seem to create a unique row function/ formula for cell Len count e.g. if the check box is True, then +13, if the check box is False +0.
The formula I produce replicates the if True/ False formula for all check boxes (thus meaning when any check box is True, 13 is added to all rows.

Are you happy to provide a little more support to help resolve this? I've tried a few ways around it but I think the bottom line is my knowledge is lacking to for an appropriate resolution - slowly but surely learning.

Thank you for your time.

[Richard Buttrey]

Hi Richard/ humdingaling,

Thank you both for you're help; I'm afraid Richard I can't get your macro to function, an error seems to display.

What error message do you get? Are you running the macro by clicking the button and is the sheet unprotected.?
It's a simple one line macro and there's no reason I can see why it won't work for you whereas it does for me.

[Bargyy]

What error message do you get? Are you running the macro by clicking the button and is the sheet unprotected.?
It's a simple one line macro and there's no reason I can see why it won't work for you whereas it does for me.

Hi Richard - it's working from the spreadsheet you've attached, so thank you for your help. Although, the function isn't available to uncheck the tick box to not add 13 (+13 if check box is true, add nothing if check box is False).

Than kyou very much for your help.

[Richard Buttrey]

Hi,

See attached. I missed the point about adding 13 although it's not clear of the 13 is added if the box is ticked or unticked.

The attached avoids the use of tick boxes which can be a bit of a pane when you need to delete them since they don't delete when you delete a row. Instead I've used a 'BeforeDoubleClick' sheet macro.

Double clicking a column D cell will alternately show a tick or blank - actually character "P" with a Windings2 font which shows as a tick. The formula in column C adds 13 if column D is a 'p'. If it should be the other way round then just adjust the C2 formula.

[Bargyy]

Hi Richard, that's better than I had expected; thank you.

From the macro you've run, I've tried to add 4 extra columns (E-F) to have no performed action/ no formulas attached other than a simple double click to show tick, and if double clicked again then remove tick based on the users needs.

I've learnt from what has been done here and can use the macro to run the intended function on a separate spreadsheet, but when it comes to running the two together I find errors occur. I gather this is because two macros can't run separately, so I'm not sure how to pull them together.

If you're happy to help on the last part of the spreadsheet please, could you advise how it may be done - I've attached the spreadsheet here so you can see what I mean if you are happy to help.

Excel Test.xlsm

[Richard Buttrey]

Hi,

Is the attached what you mean? I've commented out the code you put in since I didn't know what the reference to ChrW(&H2713) was attempting to do.