Rolling Reference number

[KaZper009]

I think this would be relatively easy... im sure...
i need to put a reference number on my quote.. but for every time it is opened... i need it to increase number by one... making the new number of the quote used...

i.e.
Reference: #00001
then after it is opened again (or has been printed or page has been refreshed...)
The new reference number will be
Reference: #00002

Cheers

Mike :)

[Bob Phillips]

An example


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim mpRoll As Long
With ActiveSheet
mpRoll = 0 ' just in case
On Error Resume Next
mpRoll = .Evaluate(ActiveWorkbook.Names("_RollingNum").RefersTo)
On Error GoTo 0
mpRoll = mpRoll + 1
ActiveWorkbook.Names.Add Name:="_RollingNum", RefersTo:=mpRoll
Application.EnableEvents = False
.PrintOut
Cancel = True
Application.EnableEvents = True
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[mdmackillop]

Is the reference used by you alone, or also by others on a network?

[KaZper009]

Hey,

It will be used on a network...
basically want the numbers to roll from 1 to as long as they can go...
thought there might have been a fuction that would be able to do this... couldn't find one when i went through them yesterday...

Thanks :)
mike

[KaZper009]

thought i would attach a screen shot well as big as i could get it, so i could upload onto here for you to see what i am doing...

hope this helps... :)

Mike

[grichey]

Won't this code have the invoice number restart to zero every time it's reopened?

[Bob Phillips]

Mine? No!

[figment]

is this creating a variable within the file? or are you storing the data to a cell, and i am just missing it?

[Bob Phillips]

It's creating a workbook variable.

[KaZper009]

The code up the top will be able to do this???

[Bob Phillips]

Yep.

[grichey]

if you save the quote every time you're finished. yes?