increasing invoice number when form is opened

jody2

New Member
Joined
Sep 27, 2002
Messages
5
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

jody2

New Member
Joined
Sep 27, 2002
Messages
5
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.
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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.
 

Dragracer

Board Regular
Joined
Jun 9, 2002
Messages
151

ADVERTISEMENT

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.
 

jody2

New Member
Joined
Sep 27, 2002
Messages
5
THANK YOU DRAGRACER finally a solution in laymans terms...God Bless you. Thanks.
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Hi Jody2

There are sites that have ready made wkbks that autonumber

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
 

Forum statistics

Threads
1,143,750
Messages
5,720,633
Members
422,294
Latest member
Gemini774

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top