# increasing invoice number when form is opened

#### jody2

##### New Member
I designed an invoice. every thing is ready to go but, I don't know how to formulate the cell that contains the invoice number to increase the number by one whenever the invoice is opened.
HELP!!!!!!Is this possible????
This message was edited by jody2 on 2002-09-28 14:36

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Al, The thing is that I kinda self taught myself and I've never used macro and I kind of need a more detailed explaination on how to do this. The invioce number is in I2. Please help with a formula. Thankx.

The theory:
Number = Number + 1

The implementation:
Cells(row,col)=Cells(row,col)+1

You would want to put the code in Workbook_Open, I guess.

personally, I think the actual number should be saved to a network location, so all computers (present and future) can access it.

Perhaps a shared workbook that captures a few essentials about the invoice.

(No, I don't have any code on this.)

If you are dealing with merged cells, within Excel 97, further refinement is needed, but this should help 95% of the situations.

Jody, no problem being self taught, you'd be surprised at how many people here are self taught. This is a great place to learn.

A formula you say. Humm that may be a tough one. You could set up Sheet2 as a log of all invoices. Put the invoice number in Column A and details in other columns. Then put in I2 on sheet1. =COUNT(Sheet2!A:A) Assuming your first invoice is #1. If you have alpha numeric numbering try something like ="B"&COUNT(Sheet1!A:A)+100 You would need to update the log sheet after completing each invoice. This is all extra work. But macros could be written to automate it.

Setting the invoice number by a macro is easy to set up. Press Alt + F11 at the same time or right click the sheet tab and select view code.

There are different types of modules in which to place code. Workbook which will activate a macro at events like open, close, save. worksheet modules which will hold macros for events like sheet change, Activate, etc. and general modules for other macros.

Now that you are in the VBA editor you will see a screen on the left hand side called Project-VBA project, if this is not visible press Ctrl + R
Doubleclick on Workbook icon from the Project screen.
A window will open on the right
Paste this code into the window

Code:
``````Private Sub Workbook_Open()
Range("I2").Value = Range("I2").Value + 1
End Sub``````

This will cause I2 to increase by one everytime the file is opened.

There is two dropdown boxes at the top of this screen.
Select the first bow and select workbook
go to the second dropdown box and you will see many other events that can be coded.

Hope this helps.

THANK YOU DRAGRACER finally a solution in laymans terms...God Bless you. Thanks.

Hi Jody2

Dave Hawley http://www.ozgrid.com
Dreamboat thewordexpert.com

Im sure theres more sorry if i forgot you but these are our MrExcel MVPs sites

HTH

Jack

Replies
0
Views
379
Replies
3
Views
331
Replies
11
Views
1K
Replies
0
Views
259
Replies
1
Views
430

1,217,677
Messages
6,137,943
Members
450,100
Latest member
mscetr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back