Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Snag in invoice generator. How to change the "series"
I have made some modifications to this code I found by McGimpsey.
Say the invoice number gets to a certain number, then I want to be able to change the 'series' say from 1000 to "A " & 0 so that the new numbers come out A0001, A0002 etc. The commented out if statement below is my best shot at it but it errors out on the line-- SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& Code works okay without the if statement. I'm thinking that the final goal would be to make the "A" a variable that can be changed on the sheet to indicate what department used a particular invoice number. If I can get past the nNumber format issue, I believe I can work out the variable issue on the myself. Thanks. Howard Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) With ThisWorkbook.Sheets("Invoice") Range("B2").ClearContents End With ActiveWorkbook.Save End Sub 'McGimpsey and Associates ' Goes in ThisWorkbook module Private Sub Workbook_Open() Const sAPPLICATION As String = "Excel" Const sSECTION As String = "Invoice" Const sKEY As String = "Invoice_key" Const nDEFAULT As Long = 1& Dim nNumber As String 'Long Dim lr As Long Dim DeptNme As String lr = Cells(Rows.Count, 11).End(xlUp).Row 'InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]) DeptNme = InputBox("Enter you Dept. Name.", "Department Name") 'Exit sub if Cancel button used or no text entered If DeptNme = vbNullString Then Exit Sub With ThisWorkbook.Sheets("Invoice") With .Range("B1") If IsEmpty(.Value) Then .Value = Date .NumberFormat = "dd mmm yyyy" End If End With With .Range("K1") If IsEmpty(.Value) Then .Value = "Used Invoice No.'s" .Columns.AutoFit End If End With With .Range("J1") If IsEmpty(.Value) Then .Value = "Department" .Columns.AutoFit End If End With With .Range("B2") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = "@" '//**** ' If nNumber = 5 Then ' nNumber = "A" & 0 ' End If '//**** .Value = Format(nNumber, "0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& .Copy Range("K" & lr).Offset(1, 0) Range("J" & lr).Offset(1, 0).Value = DeptNme End If End With End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Snag in invoice generator. How to change the "series"
Hi Howard,
Am Wed, 18 Sep 2013 23:56:04 -0700 (PDT) schrieb Howard: I have made some modifications to this code I found by McGimpsey. Say the invoice number gets to a certain number, then I want to be able to change the 'series' say from 1000 to "A " & 0 so that the new numbers come out A0001, A0002 etc. The commented out if statement below is my best shot at it but it errors out on the line-- SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& Code works okay without the if statement. I'm thinking that the final goal would be to make the "A" a variable that can be changed on the sheet to indicate what department used a particular invoice number. I hope I understood your problem. Try: With .Range("B2") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = """A""0000" .Value = Format(nNumber, """A""0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& .Copy Range("K" & lr).Offset(1, 0) Range("J" & lr).Offset(1, 0).Value = DeptNme End If End With Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Snag in invoice generator. How to change the "series"
I hope I understood your problem. Try: With .Range("B2") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = """A""0000" .Value = Format(nNumber, """A""0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& .Copy Range("K" & lr).Offset(1, 0) Range("J" & lr).Offset(1, 0).Value = DeptNme End If End With Regards Claus B. That does it, thanks Claus. I did some reading on this before I posted and double quotes were mentioned. But the proper use of them was unclear. I'm still trying to digest the usage you supplied. Works, and I appreciate it. Regards, Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Snag in invoice generator. How to change the "series"
I did some reading on this before I posted and double quotes were
mentioned. But the proper use of them was unclear... When specifying NumberFormat in VBA the value passed must be a string. To pass text withing this string you need to tell VBA this, so... "A0000" to Excel is "A" & "0000" ...which must be expressed in VBA as... ""A"" & "0000" ...so VBA knows to pass "A" as a string. Thus the concatenation of the above 2 strings into 1 continuous string is """A""0000"! Though once you set NumberFormat you don't have to use Format when setting the value... .Value = Format(nNumber, """A""0000") could just simply be... .Value = nNumber <FWIW If your invoices are generated from a pre-designed template then I strongly recommend using local scope defined names for target data fields. This will make your code easier to understand/maintain later on, and allows for revising the template without breaking the project. For example... Range("J" & lr).Offset(1, 0).Value = DeptNme ...is better understood and will require no revisions when/if the template layout gets revised if it was... Range("DeptName") = DeptName (or preferably) Range("DeptName") = sDeptName (to include a data type prefix) So your code would be better self-documenting (and require less lines) as follows... Range("InvNum") = lNextInvNum: Range("DeptName") = sDeptName SaveSetting sAPPLICATION, sSECTION, sKEY, lNextInvNum + 1 ...where the pre-designed invoice already has formatting in place that compliments the 'Values' your code will assign its various 'fields' at runtime. <more FWIW I do a lot of custom invoicing/estimating/SO/PO projects that allow for using multiple templates with entirely different layouts, but all use the same code because all use the same local scope defined names for target data fields. So if you're going to repeat invoicing projects for more people down the road it just makes sense (IMO) to minimize the work involved. I developed a generic addin template that only requires designing the 'form' (invoice/quote/SO/PO) template[s] it will use. I also have a number of pre-designed 'form' layouts that clients can choose from so I don't have to start templates from scratch to complete the project in the shortest time possible. All sample templates have 2 styles; with logo and without logo. All logos supplied by clients get reworked so their backgrounds are 100% transparent, allowing a clean look on shaded areas. Food For Though!<g When users click the 'New Invoice' menuitem the "Customer Information" userform pops up. This allows them to enter customer info starting with selecting an existing customer from a combobox OR typing in info for a new customer. The default is 'Cash Sale' in cases where repeat customer info isn't to be stored in "CusInf.dat" for reuse later. If an existing customer, the combobox allows cycling through all list items beginning with the alpha character entered. Its _AfterUpdate event auto-fills the remaining fields with the selected customer's info. (Pretty straight forward standard stuff for an invoicing app) The userform allows editing all info fields for a customer, and gives you the options for what to with the info: 1. Apply to Invoice & Update Customer Database (uses pre-defined target fields in both the template and vaCusInf) 2. Add This Information to Customer Database (a simple comma delimited text file named "CusInf.dat") 3. Apply to Invoice Only (used for new info not to be stored, or existing info) 4. Cancel All 4 buttons dismiss the userform. vaCusInf is a global scope variant that the contents of CusInf.dat get dumped into at runtime and after info updates to produce an array from which the info fields get populated. This makes managing info a trivial task, and doesn't require using the slower ADODB methods. During runtime, all customer data is accessed from vaCusInf for max efficiency. The "Customer Information" dialog can also be accessed via a menuitem so customer maintenance can be done separate from invoicing time. Each sample invoice template comes in 2 configs; with pricing and without pricing. The latter is all manual entry in the invoice 'Details' area. The pricing templates use a dropdown in the 'Item' field for auto-filling the remaining details via lookup formulas (which can be over written). Templates can be changed on the fly via the "Templates<-Set Default Template" menuitem. My point is to demonstrate the flexibility power of using pre-designed templates with pre-defined fields. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Snag in invoice generator. How to change the "series"
Though once you set NumberFormat you don't have to use Format when
setting the value... .Value = Format(nNumber, """A""0000") could just simply be... .Value = nNumber I did change the code as you mention here. Bingo, I noticed the new invoice number, A0020 was to the right side of the cell. So I did a couple more and found that A0020 + A0021 + A0022 = A0063. Not that you would ever want to add invoice number (which are often not numeric) but I found this interesting. Those little gremlins within Excel are always up to something.<g This is about as far as I am going to go with this little project. There is no looming need on the horizon waiting for a solution. I'll archive it for future reference. Although, I did offer it to a poster looking to have a sheet that produced sequential "Project Numbers". Have not heard back from him. Thanks for all the info, Garry. Regard, Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Snag in invoice generator. How to change the "series"
Though once you set NumberFormat you don't have to use Format when
setting the value... .Value = Format(nNumber, """A""0000") could just simply be... .Value = nNumber I did change the code as you mention here. Bingo, I noticed the new invoice number, A0020 was to the right side of the cell. So I did a couple more and found that A0020 + A0021 + A0022 = A0063. Not that you would ever want to add invoice number (which are often not numeric) but I found this interesting. Those little gremlins within Excel are always up to something.<g This is about as far as I am going to go with this little project. There is no looming need on the horizon waiting for a solution. I'll archive it for future reference. Although, I did offer it to a poster looking to have a sheet that produced sequential "Project Numbers". Have not heard back from him. Thanks for all the info, Garry. Regard, Howard You're welcome, Howard! I always appreciate the feedback... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? | Excel Programming | |||
=IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming |