Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto Number in Invoice
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 " |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to do a auto number generater | Excel Worksheet Functions | |||
Generate Auto Order Number | Excel Discussion (Misc queries) | |||
Auto Number | Excel Discussion (Misc queries) | |||
Auto number invoices in Excel | Excel Worksheet Functions | |||
assign auto number and auto date | Excel Discussion (Misc queries) |