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

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

"John Carter" wrote in message
...
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

JE McGimpsey has a site that deals with that

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

--
Biff
Microsoft Excel MVP


"L. Howard Kittle" wrote in message
...
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

"John Carter" wrote in message
...
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

Thanks, T. Valko,

That is the site I was thinking of.

Regards,
Howard

Valko" wrote in message
...
JE McGimpsey has a site that deals with that

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

--
Biff
Microsoft Excel MVP


"L. Howard Kittle" wrote in message
...
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

"John Carter" wrote in message
...
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

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


"L. Howard Kittle" wrote in message
...
Thanks, T. Valko,

That is the site I was thinking of.

Regards,
Howard

Valko" wrote in message
...
JE McGimpsey has a site that deals with that

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

--
Biff
Microsoft Excel MVP


"L. Howard Kittle" wrote in message
...
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

"John Carter" wrote in message
...
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