number for invoices and forms automatically increasing by 1 each time I open the template

Fedjwick · #1 04-28-2017, 01:07 PM

How can I establish a number for forms or invoices that AUTOMATICALLY will increase by one (1) each time I open the template to save a new document? As is I have to manually change the order number or invoice number and sometimes I forget which leaves me with more than one order or invoice with the same number. Seems like an easy enough task but I can't find a way to do it on my own. Need specific step by step instructions.

Logit · #2 04-28-2017, 04:48 PM

Here is the basics of what you are requiring, assuming your Invoice Template is part of the Sheet and not a Form.

Create a Routine Module and paste this code into the module :

Code:

Option Explicit
Sub PlsOne()
Dim num As Integer
    Range("A1").Select
    num = Range("A1").Value
    num = num + 1
    Range("A1").Value = num
End Sub

In ThisWorkBook module, paste this code :

Code:

Option Explicit

Private Sub Workbook_Open()
    PlsOne
End Sub

Each time you open the workbook, the number in A1 of Sheet 1 will increment by one
number.

You can edit the code to have this applied to any sheet and/or any cell.

Fedjwick · #3 04-30-2017, 11:45 PM

This sounds good, if only I knew how to "Create a Routine Module," and a "ThisWorkBook Module" to enter the codes you have listed.
I looked in the help menus and nothing comes up for either module in telling me how to create either.
A friend sent me a tutorial link and I will look at it in the meantime,
but the info you sent me,
though quite thorough in the description of the code needed to be placed within these "Modules,"
does not tell me how to create or find such.
I'm still stuck.

Logit · #4 05-01-2017, 05:38 AM

Let's say you are working with Sheet1. Sheet1 is where you want to create your invoice. RIGHT CLICK the Sheet1 tab at the bottom of the workbook window.
Select VIEW CODE.

You now see a large open area in front of you with a smaller side window to the left. There should actually be two smaller windows on the left. One above the other. If you don't see 2, up top on the menu bar click VIEW / PROPERTIES WINDOW.

The top window allows you to access the code area for each sheet and ThisWorkBook module. If you are instructed to access a Sheet level module (or something similar to that description) you would double click on one of the sheet names and the large window to the right should turn white in color. That is the area you would enter sheet level module code.

The small window on the bottom left provides the various properties for any number of items you may be working with (userform, command button, image control, label, etc.).

But for right now let's only concentrate on the two macros provided previously.

Double click on ThisWorkbook in the top left window. The large right sided window will turn white in color.
Paste this code into that area:

Code:

Option Explicit

Private Sub Workbook_Open()
    PlsOne
End Sub

Now, to create a ROUTINE MODULE ... on the menu bar up top, click INSERT / MODULE. You should see a MODULE 1 appear in the upper left smaller window. Double click that item (Module1).

Again, the large right sided window is white and you will paste this code into the module :

Code:

Option Explicit
Sub PlsOne()
Dim num As Integer
    Range("A1").Select
    num = Range("A1").Value
    num = num + 1
    Range("A1").Value = num
End Sub

You should now save the workbook as a 'macro enabled' workbook. It's called 'macro enabled' because you have created at least one macro of code. In this case, you actually have two macros: one in the ThisWorkbook module and one in a Routine Module.

When you save the workbook, name it whatever you want - save it where you can get to it easily (I usually save to the Desktop until I've finished a project ... makes it easy to get to and fast) and be certain when saving that you do so using the .XLSM extension. XLSM is one of any number of workbook types - but this one means the workbook contains macros. Think of it like: XL = Excel , M = Macro. You will go to the upper left corner of the workbook, click on the Excel Button (has the Microsoft Logo on it) and choose SAVE AS.

Now, if everything was done correctly, close the workbook, reopen it and cell A1 on Sheet 1 should display a number. If you close and reopen the workbook again, that number should increment by one.

You can now design your invoice on Sheet1 and by editing the code above where it says "Range("A1").Value ... you can change the A1 to whatever cell you are using for the Invoice Number.

I'm a visual type person. Book learning only takes me so far. I learn best by hands on. The best way to learn Excel and VBA is to actually get in there .. create something .. and see what it does. Then change something and see what that does. There are tons of examples on this forum, other forums and tons of websites. Start small and build up. It isn't difficult .. I find it to be fun and rewarding ... and best of all you can't break anything by trying and experimenting. The code will either work or it won't. I learn really well from my mistakes.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Ms Word Template with VBA forms not working in SharePoint	KangarooJack	Word VBA	0	01-28-2016 02:11 PM
Time Stamps automatically changed to same time!	hydrogyny	Word	4	02-09-2015 08:57 PM
I want Word to open a normal blank template upon launch, not ask me every time!	rganzen1	Word	2	11-17-2013 05:42 PM
Labeling sequential forms with a unique number	krau0231	Word	1	10-18-2012 03:33 PM
Add increasing number at document opening	HidExp	Word	6	09-17-2012 12:28 AM