I AM WONDERING WHAT HAPPENED TO PAMMYJ. DID SHE GET HER PROBLEM RESOLVED OR
FALL OFF THE PLANET ? PAMMYJ YOU OUT THERE ?? WHAT WERE YOUR RESULTS ??
LIZARD1SOCAL
--
lizard1socal

We probably scared her off, or she felt the thread was hijacked with
questions you asked, or she may have gone off-line like you and I did, but
with Rookie 1st class, to deal with it all one-on-one.

By now you should have the two working invoices with semi- and fully-
automatic numbering. Perhaps some feedback on those once you've had a chance
to evaluate them might help. If it turns out you think those are handling
things properly, perhaps Pammi J would like to look at them also.

"lizard1socal" wrote:

I AM WONDERING WHAT HAPPENED TO PAMMYJ. DID SHE GET HER PROBLEM RESOLVED OR
FALL OFF THE PLANET ? PAMMYJ YOU OUT THERE ?? WHAT WERE YOUR RESULTS ??
LIZARD1SOCAL
--
lizard1socal

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

OK we may be getting somewhere - i have been twiddling with the code and in
the `this workbook` iv pasted this
Private Sub Workbook_Open()
SetUpNewClient
End Sub
Now that when the workbook is opened auto runs the maro and starts a new
number on a blank invoice. --- One problem i cant save the open invoice - i
needed to be able to file - save copy as - and then it would have that unique
inv number along with all the cust details. but when i save it increments on
the invoice number again - any suggestions from here please?
many thanks

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Semi-Automatic version - double-click in a specific cell to get a new invoice
number.

The code for ThisWorkbook:
-----

Private Sub Workbook_SheetBeforeDoubleClick( _
ByVal Sh As Object, _
ByVal Target As Range, _
Cancel As Boolean)

If Not Intersect(Target, Range("M3")) Is Nothing Then
Target.Value = IncrementInvoiceNumber
Cancel = True
End If

End Sub

------
The code for the code module
------

Public Function InitializeInvoiceNumberBase() As Boolean
Dim anyInvoiceNumber As String

On Error Resume Next
anyInvoiceNumber = Names("LastUsedInvoiceNumber").RefersTo
If Err < 0 Then
'doesn't exist create it
ActiveWorkbook.Names.Add Name:="LastUsedInvoiceNumber", _
RefersToR1C1:="=0"
Err.Clear
End If
On Error GoTo 0
End Function

Public Function IncrementInvoiceNumber() As Long
InitializeInvoiceNumberBase ' in case it doesn't exist
IncrementInvoiceNumber = GetInvoiceNumber + 1
UpdateInvoiceNumber IncrementInvoiceNumber
End Function

Public Function UpdateInvoiceNumber(newNumber As Long) As Boolean
InitializeInvoiceNumberBase ' in case it doesn't exist
Names("LastUsedInvoiceNumber").RefersToR1C1 = "=" & Trim(Str(newNumber))
UpdateInvoiceNumber = True ' successful
End Function

Public Function RollbackInvoiceNumber() As Long
'this is not currently used, but
'provided for anyone who wants to implement roll-back
InitializeInvoiceNumberBase ' in case it doesn't exist
RollbackInvoiceNumber = GetInvoiceNumber - 1
'prevent going into negative numbers
If RollbackInvoiceNumber < 0 Then
RollbackInvoiceNumber = 0
End If
UpdateInvoiceNumber RollbackInvoiceNumber
End Function

Private Function GetInvoiceNumber() As Long
Dim tmpString As String
tmpString = Names("LastUsedInvoiceNumber").RefersTo
GetInvoiceNumber = Val(Right(tmpString, Len(tmpString) - 1))
End Function

Sub OpenInvoiceNumberManager()
InvoiceNumberMgr.Show
End Sub

-----

Finally, there is a userForm named InvoiceNumberMgr, it has 1 text box and 3
command buttons. The text box name is txtFirstInvoiceNumber.
The 3 command buttons have names of:
cmd_Update
cmdClose
cmdResetForm


This is the code that goes with the form:
-----

Private Sub cmd_Update_Click()
Dim StartingNumber As Long

If Me!txtFirstInvoiceNumber.Text = "" Then
MsgBox "You must provide a starting/new invoice number."
Me!txtFirstInvoiceNumber.SetFocus
Exit Sub
End If
'is entry in txtInvoiceNumberCell a valid address?
StartingNumber = Abs(Int(Val(Me!txtFirstInvoiceNumber.Text)))
StartingNumber = StartingNumber - 1
If StartingNumber < 0 Then
StartingNumber = 0
End If
'actually set the invoice number value
UpdateInvoiceNumber StartingNumber
Me.Hide
End Sub

Private Sub cmdClose_Click()
Me.Hide
End Sub

Private Sub cmdResetForm_Click()
Me!txtFirstInvoiceNumber.Text = ""
Me!txtFirstInvoiceNumber.SetFocus
End Sub


"JLatham" wrote:

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

The fully automatic version.

Code for the code module is the same as the other above.

Code for the user Form is the same as the other above.

There is no code associated with ThisWorkbook, but instead there is code to
go with the worksheet(s). Also, the basic sheet from which all other invoice
sheets must be referenced in the code along with which cell is to get the
invoice number on new sheets created from it. In the 'template' sheet, that
cell should remain empty.

Private Sub Worksheet_Activate()
'change "Invoice Number" to the name you give to
'your own 'template' sheet in the workbook
If ActiveSheet.Name = "Invoice Number" Then
Exit Sub ' do not put number on basic template sheet
End If
'change "M3" to what ever cell on your sheet(s) is to
'contain the invoice number
'make certain this cell is empty on your template sheet
If IsEmpty(Range("M3")) Then
Range("M3") = IncrementInvoiceNumber
End If
End Sub


to use this version, simply select the 'template' sheet and use
Edit | Move or Copy Sheet
and be sure to check the box next to [Create a Copy]
The new sheet will have next invoice number in sequence in it.

Alternatively, you can right-click on that sheet's tab and choose [Move or
Copy] from the popup list and continue from there.


"JLatham" wrote:

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

Hi
i have emailed you my inv template. The only thing i cannot get to work is
to save a copy of the template with inv no and cust details - it seems to
carry the macro with it - is there any way to save a copy without the macro,
so it keeps the details and inv no given?

"JLatham" wrote:

The fully automatic version.

Code for the code module is the same as the other above.

Code for the user Form is the same as the other above.

There is no code associated with ThisWorkbook, but instead there is code to
go with the worksheet(s). Also, the basic sheet from which all other invoice
sheets must be referenced in the code along with which cell is to get the
invoice number on new sheets created from it. In the 'template' sheet, that
cell should remain empty.

Private Sub Worksheet_Activate()
'change "Invoice Number" to the name you give to
'your own 'template' sheet in the workbook
If ActiveSheet.Name = "Invoice Number" Then
Exit Sub ' do not put number on basic template sheet
End If
'change "M3" to what ever cell on your sheet(s) is to
'contain the invoice number
'make certain this cell is empty on your template sheet
If IsEmpty(Range("M3")) Then
Range("M3") = IncrementInvoiceNumber
End If
End Sub


to use this version, simply select the 'template' sheet and use
Edit | Move or Copy Sheet
and be sure to check the box next to [Create a Copy]
The new sheet will have next invoice number in sequence in it.

Alternatively, you can right-click on that sheet's tab and choose [Move or
Copy] from the popup list and continue from there.


"JLatham" wrote:

Pammi J,
Maybe download, unzip and look at the two files in this
http://www.jlathamsite.com/uploads/invoices.zip

Two slightly different versions of the same invoice workbook. Each is
'self-contained' - there is no .txt file or such with either one. I call one
semi-automatic, the other fully-automatic. Each uses a name stored in the
workbook itself to hold the last used invoice number. In the semi-automatic
version, you double-click in cell M3 on the invoice sheet to get a new
number. In the fully automatic version, you make a copy of a 'template'
sheet that is in it and when the copy is made it is given the next number in
sequence. It really is pretty much that simple for each.

There is one macro in both that is visible with Tools | Macro | Macros that
is a setup form to allow you to set the initial invoice number with, or
change the next number to use if you wish to later. In both files there is
one code module that you can copy from into any other workbook and it should
work fine, the code is not workbook or worksheet dependent and will
automatically create the 'name' used for you in a new book if it does not
already exist when you first start using it.

There is the userform in each, which you can use File | Export and File |
Import from the VB Editor to move from one workbook to another.

In the semi-automatic version there's a little coding in the Workbook code
segment to be copied to any new book, with a change needed to indicate which
cell is to have the invoice numbers placed into it (your E5 - right now it is
at M3). In the fully automatic version there's no workbook code, but the
worksheet(s) have some code in them, same deal: change M3 to point to the
proper cell to receive the invoice number.

If you like the way either of them works and have trouble porting the code
over to the invoice file you are trying to get set up, I'd be more than happy
to help: do as lizard1socal did - send email to HelpFrom @ jlathamsite.com
with your Excel file attached and I'll dump the needed pieces into it and
return it to you.

Anyone else is welcome to download that .zip file while it's still on the
site, the original file is (apparently) one of the invoice templates provided
with Excel 2000, and the code is like any code put up here by me: free to use
if it helps you, just don't try making $$ off of my work without cutting me
in on the deal <g.

"Pammi J" wrote:

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"