Increase Invoice Number for Excel 16

[jjsays]

Hi guys, new to the forum so forgive any faux pas please.

This question relates to Microsoft Office 16 ( with the workbook shared to MS Office 13 and 10 occasionally ) I have searched these forums for help and tried several solutions but as yet have found no recent or relevant to my version solutions - the attempts using info from these forums so far have had no success so please forgive me for asking the same question you get asked every other week. I have also followed the links to McGimpsey and Associates several times with eiter no luck or too little understanding.


My husband has a tiny business that requires a very simple cash sale/invoice template. He is an administrators nightmare and so is his buddy that occasionally works for him. Every time I have created an invoice template for them they backspace through my formulas and muck things up. Yes, I should have protected the worksheets but I was new to excel too.

This time I have created the bees knees of templates for them and locked and hidden any cell with a formula and have protected both the sheet and the workbook - woohoo - no more foul ups!

Anyway - Question 1.
I have a cell - I8 which contains the function =""&TEXT(TODAY(),"ddmmyy")&N3
Cell N3 holds a 4 digit number which I want to increase by 1 each time the template is saved.
I have tried several VBA macros with no luck.

The latest one has been this one

Please Login or Register  to view this content.

which of course I snagged from these forums.

Note, for some reason Excel has named my first sheet "sheet50" and the second one "sheet1". I have named them 'Basic Invoice" and "Products" respectively. I am getting a subscript out of range error, runtime error 9 and a variety of other error messages whenever I try anything.


I have even tried a very simple macro

Please Login or Register  to view this content.

Had problems with this one too so now I am stumped.

Please note that these are my first ever attempts at VBA so please excuse the rookie mistakes.

My other question relates to saving the file to a speciic path. I want the guys to be able to open the template from the desktop but I want them to be able to save the filled in file with the invoice number in the filename (maybe even the client name ) to a particular place - even if that place is another folder on the desktop. The other guy who works with my husband would love to see the invoices saved in folders that pertain to the month they were created in but I realise that seems to be expecting a great deal from an app and a person ( me ) with very basic excel knowledge/a

Any and all help appreciated and thanks to those willing and able to help others on these forums - You guys have already taught me a lot.

[protonLeah]

Unfortunately ther are two problems:

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

---

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[jjsays]

Thanks very much for your help. Appreciate the tips and will read the rules with more care in future.

[Trebor76]

Hi jjsays,

Welcome to the forum!!

You could name the cell containing the invoice number (I named my cell MyInvNumber) and use this code:

Please Login or Register  to view this content.

This way the cell position can move and / or the tab name change but as long as the named range doesn't become corrupt all will be good.

HTH

Robert

[jjsays]

Hi Robert, thanks for that - I do not seem to be getting any debug errors anymore - but nothing seems to be happening. I am wondering if I am putting it in the right place. I have opened VBA, then the VBA Project via CTRL+R, selected 'This Workbook' and have pasted the code straight into the 'Workbook' pane. Is this correct? Really appreciate the simplicity of the code too btw. Excellent.

EDIT: I have managed to get it to work if, when I open the file I 'enable macros" then hit Alt+F8 and choose the macro listed and hit run. This is doable of course but I would prefer it if the process was idiot proof

[Trebor76]

Is this correct?

That's hard for me to answer. Could you attach the workbook (devoid of any sensitive data) so I can have a look?

[jjsays]

ExcelForumCopy.xlsx

Thank you Robert, hoping I did this right. Appreciate the help.

[Trebor76]

Hi jjsays,

The invoice number now increments by 1 on the attached. There is a message about saving ActiveX controls each time the workbook is saved which I have no idea what's causing it (I'm pretty sure it's nothing to do with what I've done)???

Regards,

Robert

[jjsays]

Oh My Goodness! Thank you so very much Robert.

I can see the changes you have made and I confess I never would have got there myself so I am very, very grateful. Most appreciated. I am not getting any ActiveX errors either - Yay!

[Trebor76]

Thanks for the feedback (and the rep) and you're welcome