Snag in invoice generator. How to change the "series"

[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.

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

 

[Claus Busch]

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.

 

[Howard]

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

 

[GS]

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 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

 

[Howard]

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 thecell. 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

 

[GS]

Though once you set NumberFormat you don't have to use Format when

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