Help
How to roll to next invoice number automatically every time when opening the
workbook Thanks So Much Greg |
Help
Hi Greg,
Great solutions available by Jim : http://www.mcgimpsey.com/excel/udfs/sequentialnums.html HTH Cheers Carim |
Help
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 |
Help
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 |
Help
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 |
Help
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 |
Help
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 |
Help
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 |
Help
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 |
All times are GMT +1. The time now is 03:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com