Assign a number in excell 1

[funkjunky]

I want to assign a number in excel that will increase every time the work book is opened. This is to be used as an invoice number. Where can I get the code for this?

 

[Cresby]

put it in an autorun macro. a Macro that always executes when the file is opened.

I can't remember what I called it but in Access & Excel they are called different things. Autorun or Autpen or AutoExec try those words in the help file. It is well hidden - I will post when I find it - though I expect others will get here first.

I am very wary of them because that is mechanism the "I love you" Melissa virus used. In short I treat all e-mailed .doc .xls .dbm etc files with suspician.

 

[Cresby]

call the macro Autpen in Excel (AutoExec in Access)

Sub Autpen()
Sheets("sheet1&quot.Range("A1&quot.Value = Sheets("sheet1&quot.Range("A1&quot.Value + 1
End Sub

the sheet and cell value should be obvious.

If you are new to macros I strongly recommend using the record function and take a gander at what Excel thinks you havejust done manually. Even years later i find it easier to do this for anything that is accessable from the mouse.

A warning though - if having difficulty with a manual v recorded sequence read the help file in detail - the Macro record is not infallible!!!! I have faulted it several times.

 

[funkjunky]

Thanks Cresby Worked great!

 

[funkjunky]

Spoke to Soon. The problem is it only works if you save the work sheet. This defeats the object a bit as you would have to remove all the data entered in the sheet B4 closing. any way around this?

 

[johnwm]

Add a new sheet just for this purpose?
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[funkjunky]

I Dont Under Stand!

 

[HobbitK]

IF the ONLY time that the spreadsheet is being opened is through your program, then it would be very simple to use the SaveSettings and GetSettings methods in VB.Asign the number from GetSettings to a global level variable, then before you actually assign to SaveSettings, get the number from the global variable add 1 then save it back to the registry. Doing this in VB obviously will not work if the spreadsheet has the ability to be opened at any time other than through your program. Look in Excel help for save/get settings .. perhaps there is a method to accomplish the same thing that is exclusive to Excel.

 

[Cresby]

Am I missing something here?
Surely if you add a save instruction immediately after the index instruction (don't "save as" unless you set an ignore replies mode - no I can't remember the instruction)

Oh of course what about the Auto_Close macro?

Sub Auto_Close
Sheets("sheet2&quot.Range("a1:zz1000&quot.Clear
ActiveWorkbook.Save
End Sub

Choose your range

In help go look for AutoActivate and see also

 

[funkjunky]

This is nearly there. The project is a sales invoice. with the "dont save as" command all the sheet is saved when you close. We need to be able to close the sheet without saving the info entered, but for the invoice number to advance by 1 the next time you open the work book. The work book is not opened by a program just manually opened by the operator when requiered.