auto number question

[John Carter]

I have a template that is used as a quote sheet. We would like to have it
create a quote number automatically when the file is saved and not change
when the file is reopened.
We have thought about using the "now" function then kill the formula when
the file is saved on save but have not figured out how to do it.
any help would be appreciated.

thank you,
m

 

[L. Howard Kittle]

Hi John,

I have some code that may do what you want. There is some MVP code out
there to increment invoice numbers and deals with invoices (quotes) that are
started and then canceled... so the number, if canceled, will not increment,
if processed then does increment. May be exactly what you want

JE McGimpsey has a site that deals with that, if I remember correctly. You
could Google that and see if that helps. I thought I had it in my archives
but can't find it. It takes on the many aspects of starting a quote and
then dismissing it and the number does not increase, or if you instigate it
then the number does increase.

A simple example might look like this. You would have to make sure you
click a button to increase the quote number, or click a button that does not
increment the number.

Sub OneMore()
Range("A1").Value = Range("A1").Value + 1
End Sub

My code relates to presenting a quote number that relates to a specific
customer, by name or number, and a specific service, by name or number and I
am sure you can add a time stamp to the end. It is a bit on the amateur
side but I believe it can be cleaned up to suit. It is a bit sloppy...!


Option Explicit

Sub SerialNo()
Dim h As Integer
Dim i As String
Dim j As String
Dim k As Integer
Dim l As Integer

l = Range("D1").Value + 1
Range("B2").Value = Chr(64 + l)

h = Range("A1").Value
i = Range("A2").Value
j = Range("A3").Value
k = Range("A4").Value
l = Range("D1").Value + 1
Range("B2").Value = Chr(64 + l)

Range("C100").End(xlUp).Offset(1, 0).Value = "SN" & "-" _
& h & " " & i & " / " & j & "-" & k
'Range("A4").Value = Range("A4").Value + 1

If l = 26 Then Range("B1").Value = Chr(65 + 1)
If l = 26 Then l = 0
Range("D1").Value = l

End Sub

HTH
Regards,
Howard

 

[T. Valko]

JE McGimpsey has a site that deals with that

http://mcgimpsey.com/excel/udfs/sequentialnums.html

 

[L. Howard Kittle]

Thanks, T. Valko,

That is the site I was thinking of.

Regards,
Howard

 

[John Carter]

Thank you Howard and T. Valko. I think this is probably what I need. I
will try it out when I get time and will report back the results.

thanks again,

John