How to roll to next invoice number automatically every time when opening the
workbook
Thanks So Much
Greg

Hi Greg,


Great solutions available by Jim :
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

HTH
Cheers
Carim

Thanks So Much Carim God Bless You It worked Great
Greg

"Carim" wrote:

Hi Greg,


Great solutions available by Jim :
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

HTH
Cheers
Carim

Hi Carim I spoke to soon I can't get it to work I copied your info to VB but
it wont create the macro How do point it to an specific cell I am lost
Thanks
Greg
"Carim" wrote:

Hi Greg,


Great solutions available by Jim :
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

HTH
Cheers
Carim

Greg,

The code has to copied into ThisWorkBook module, not into a Sheet
module or a regular module ...
In Jim's example the cell B2 is the cell used for the invoice number
which gets automatically incremented ...

HTH
Cheers
Carim

Sorry Carim I guess I am too dumb Is there a ready made macro I could find
I have a number sequence MT000001 which has to move up 1 number every time I
open the sheet on cells B5 to I5
Got any ideas
Thanks so Much
"Carim" wrote:

Greg,

The code has to copied into ThisWorkBook module, not into a Sheet
module or a regular module ...
In Jim's example the cell B2 is the cell used for the invoice number
which gets automatically incremented ...

HTH
Cheers
Carim

Greg,

Once you got the sequence number working, you can easily format it as
follows :
Format Cell Number Custom :
"MT"00000
and it will produce your result.

HTH
Cheers
Carim

I tried that code, then formatted cell B2 like Carim said.
I had to comment out the other formatting code then it worked nicely.
Also, i noticed you have to delete value from cell B2 before saving
else
the macro wont find it "empty" and will not work.

Private Sub Workbook_Open()
Const sAPPNAME As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPNAME, sSECTION, _
sKEY, nDEFAULT)
'.NumberFormat = "@"
.Value = nNumber ' Format(nNumber, "000000")
SaveSetting sAPPNAME, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

Jim

Carim wrote:
Greg,

Once you got the sequence number working, you can easily format it as
follows :
Format Cell Number Custom :
"MT"00000
and it will produce your result.

HTH
Cheers
Carim

Sorry I guess I am to dumb to understand I could n't get it to work

Thansk So Much Both of you God Bless

"Jim" wrote:

I tried that code, then formatted cell B2 like Carim said.
I had to comment out the other formatting code then it worked nicely.
Also, i noticed you have to delete value from cell B2 before saving
else
the macro wont find it "empty" and will not work.

Private Sub Workbook_Open()
Const sAPPNAME As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPNAME, sSECTION, _
sKEY, nDEFAULT)
'.NumberFormat = "@"
.Value = nNumber ' Format(nNumber, "000000")
SaveSetting sAPPNAME, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

Jim

Carim wrote:
Greg,

Once you got the sequence number working, you can easily format it as
follows :
Format Cell Number Custom :
"MT"00000
and it will produce your result.

HTH
Cheers
Carim