help with numbers

Posted by Julian Vigo on June 23, 2001 12:05 PM

I am using excell for the first time and i used the template for receipts that came with the italian version of excell. I have configured the receipt as I would like and have a block where the recipt number goes. I also saved this file as a module so that each time it is opened a new file comes up and i save it as receipt 001, receipt 002, etc. How can I make the receipt automatically number itself based on the previous receipts already saved in the folder. For instance, I have gone up to receipt 009 and i want the file to automatically number itself as receipt 010 both in a cell and as the file name.

any help is greatly appreciated!

thanks in advance
julian

Posted by Joseph Was on June 24, 2001 10:06 AM

How to auto increase invoice numbers!

Have a starting value in your "Invoice #" cell. Note the cell address. On the Macro page, name your macro, I used, "Increase_B8."

B8 being the cell that has the invoice #.
Then Copy & paste this: [B8].Value = [B8] + 1

Or,

If you have macros in the application already, Edit any one and add all the code below any "End Sub" with copy & paste. Change the cell address to your invoice cell address and edit the macro title. Close macros.

Build button, View-Toolbars-Forms select button, place it on your sheet; assign your macro. Change the button title, by clicking on the lable, delete or backspace the old lable out, then type your own.

This will increase the invoice # by one each time the button is clicked.

This is a working VB code macro, for increasing the value of cell B8 by one each time it is clicked;

Sub Increase_B8()
[B8].Value = [B8] + 1
End Sub

Chao. JSW

Posted by Joe Was on June 24, 2001 10:16 AM

Re: How to auto increase invoice numbers 2nd way!

Note the last macro can also be activated bt a "Hot-key" in place of the form button; Macro - Macros select your macro hit "Options" and pick a keyboard letter. When you then, Hit Your Selected: Ctrl-key the macro will automatically increase your invoice number, by the macro code listed in the other note.

If you are using a master input form, which is then saved with a different name for each order?
You could write the macro so it will increase the invoice number each time the blank form is oppened.

To do this you need to place the VB code in the "Sheet Tab" View code page. It must be named by the On_Open event for the worksheet.

If you need help with this let me know? JSW

Posted by Joe Was on June 24, 2001 10:26 AM

Private Sum Workbook_Open(), way!

Sheet Tab - View code

Private Sub Workbook_Open()
[B8].Value = [B8] + 1
End Sub

You must save the blank worksheet with the same name befor you add any information to it or the blank input sheet will start with 1001 and goto 1002 each time it is opened! If you save the blank input sheet with 1002 the next time you open it it will be 1003 and so on.

You could add the code to save the workbook after the increase invoice code! JSW

Posted by Julian Vigo on June 24, 2001 3:40 PM

Re: Private Sum Workbook_Open(), way!

thanks for the help. where would i put an on_open event for the worksheet? that is under what menu do i go...i am so new to this...
thanks again

julian

Posted by Joe Was on June 26, 2001 5:41 AM

Its not on the menu

The top line of my last msg: Right click the lower left sheet tab lable for your sheet, then select "View code" place a copy (Copy & Paste) the VB code Macro from this BB to the code page! JSW thanks for the help. where would i put an on_open event for the worksheet? that is under what menu do i go...i am so new to this...