Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
Hi
i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
Have your code in a master workbook, with an invoice template worksheet
within it. Then copy that template to a new workbook, like so ThisWorkbook.Worksheets("Template").Copy With ActiveWorkbook 'your code End With just make sure that all of the code works on the actoive workbook from that point on. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Pammi J" wrote in message ... Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
PammiJ,
Once I get the answers to email I sent in response to yours this morning (which may be waiting at home now), I'll be working back in that to get the whole thing set up the way you folks there need it to be so that it operates the way you need. "Pammi J" wrote: Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
Bob - to explain. First must determine which of 3 possible workbooks she's
talking about. One was developed in a long thread elsewhere and uses an external .txt file to contain the last invoice number used. Two others use a Name to keep the number internal to the file. Earlier versions had the code updating the invoice number entry every time the file was opened - that was from original code obtained from another site. I've modified the code in that one, and set up code in the two other versions to never insert an invoice number into a cell with an invoice number already in it. Consider the version with the external .txt file tracking last used invoice number: you open the template file, it pops the next invoice number into the proper cell. You open that same file again, the code will not do that because it will see there is already an invoice number in it. In the case of the one with the invoice number kept internally, there is a "template" sheet that has no entry in the invoice number cell - the code in the sheet's Activate event tries to put a number in it UNLESS: there's already a number in it, OR if the sheet name is the template sheet's name. There is a button on the sheet to create new invoice which simply copies the 'template' sheet which in turn activates the new sheet, which automatically assigns that new sheet the next invoice number in sequence. All that's left to be decided, as far as I know, is which one of the two PammiJ feels will work best with the way they do the work at their establishment. "Bob Phillips" wrote: Have your code in a master workbook, with an invoice template worksheet within it. Then copy that template to a new workbook, like so ThisWorkbook.Worksheets("Template").Copy With ActiveWorkbook 'your code End With just make sure that all of the code works on the actoive workbook from that point on. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Pammi J" wrote in message ... Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
You obviously know a lot more about the situation than me, but whichever
course they take, my advice still holds good does it not? The post was asking how not to take the code with the template. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bob - to explain. First must determine which of 3 possible workbooks she's talking about. One was developed in a long thread elsewhere and uses an external .txt file to contain the last invoice number used. Two others use a Name to keep the number internal to the file. Earlier versions had the code updating the invoice number entry every time the file was opened - that was from original code obtained from another site. I've modified the code in that one, and set up code in the two other versions to never insert an invoice number into a cell with an invoice number already in it. Consider the version with the external .txt file tracking last used invoice number: you open the template file, it pops the next invoice number into the proper cell. You open that same file again, the code will not do that because it will see there is already an invoice number in it. In the case of the one with the invoice number kept internally, there is a "template" sheet that has no entry in the invoice number cell - the code in the sheet's Activate event tries to put a number in it UNLESS: there's already a number in it, OR if the sheet name is the template sheet's name. There is a button on the sheet to create new invoice which simply copies the 'template' sheet which in turn activates the new sheet, which automatically assigns that new sheet the next invoice number in sequence. All that's left to be decided, as far as I know, is which one of the two PammiJ feels will work best with the way they do the work at their establishment. "Bob Phillips" wrote: Have your code in a master workbook, with an invoice template worksheet within it. Then copy that template to a new workbook, like so ThisWorkbook.Worksheets("Template").Copy With ActiveWorkbook 'your code End With just make sure that all of the code works on the actoive workbook from that point on. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Pammi J" wrote in message ... Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
Bob,
Yes your solution is possible. If PammiJ uses the version that does not have code associated at the Worksheet event level. There's one version that does use code in the sheet's _Activate() event. I'm kind of confused at this point - First, I'm not sure which version she is speaking about, or how she's trying to use it/them. NONE of the 3 options I gave her will automatically renumber a sheet once an invoice number has been placed in the cell where it is to appear on an invoice sheet. I'll have to test to see if any of them would do so if the sheet were separated out and moved into another book or something like that. I have not received email today with reply to my request for her to explain her process for creating and saving invoices. I'm actually not even sure she's using any of them from a .xlt file vs just reusing a .xls file. As a matter of fact, two of the options actually initially required that she click a button to get a new invoice number, the other (3rd) had a sheet to use a template for new invoice sheets with blank cell where invoice number is to go, and when that sheet was copied to new sheet, then the invoice number was placed into the new sheet with the source sheet keeping a blank cell there for future reference: This 3rd version that works by assigning invoice number to newly created sheets from the one has this code at the Worksheet level: Private Sub Worksheet_Activate() If ActiveSheet.Name = "Invoice" Then Exit Sub ' do not put number on basic template sheet End If If IsEmpty(Range(InvoiceNumberLocation)) Then Range(InvoiceNumberLocation) = IncrementInvoiceNumber End If End Sub Where "Invoice" is the sheet name of the 'template' sheet; InvoiceNumberLocation is the cell where the invoice number is to be placed; and IncrementInvoiceNumber is actually a function that determines what the next number is to be. So, Rule #1 - don't put number on the template invoice sheet, and Rule #2 - don't overwrite an existing invoice number already on a sheet. "Bob Phillips" wrote: You obviously know a lot more about the situation than me, but whichever course they take, my advice still holds good does it not? The post was asking how not to take the code with the template. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bob - to explain. First must determine which of 3 possible workbooks she's talking about. One was developed in a long thread elsewhere and uses an external .txt file to contain the last invoice number used. Two others use a Name to keep the number internal to the file. Earlier versions had the code updating the invoice number entry every time the file was opened - that was from original code obtained from another site. I've modified the code in that one, and set up code in the two other versions to never insert an invoice number into a cell with an invoice number already in it. Consider the version with the external .txt file tracking last used invoice number: you open the template file, it pops the next invoice number into the proper cell. You open that same file again, the code will not do that because it will see there is already an invoice number in it. In the case of the one with the invoice number kept internally, there is a "template" sheet that has no entry in the invoice number cell - the code in the sheet's Activate event tries to put a number in it UNLESS: there's already a number in it, OR if the sheet name is the template sheet's name. There is a button on the sheet to create new invoice which simply copies the 'template' sheet which in turn activates the new sheet, which automatically assigns that new sheet the next invoice number in sequence. All that's left to be decided, as far as I know, is which one of the two PammiJ feels will work best with the way they do the work at their establishment. "Bob Phillips" wrote: Have your code in a master workbook, with an invoice template worksheet within it. Then copy that template to a new workbook, like so ThisWorkbook.Worksheets("Template").Copy With ActiveWorkbook 'your code End With just make sure that all of the code works on the actoive workbook from that point on. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Pammi J" wrote in message ... Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
Pammi
I think you need to add an If statement =IF(E5="",your lookup,E5) or =IF(E5<"",E5,your lookup) different versions of the same equation. Lou "JLatham" wrote: Bob, Yes your solution is possible. If PammiJ uses the version that does not have code associated at the Worksheet event level. There's one version that does use code in the sheet's _Activate() event. I'm kind of confused at this point - First, I'm not sure which version she is speaking about, or how she's trying to use it/them. NONE of the 3 options I gave her will automatically renumber a sheet once an invoice number has been placed in the cell where it is to appear on an invoice sheet. I'll have to test to see if any of them would do so if the sheet were separated out and moved into another book or something like that. I have not received email today with reply to my request for her to explain her process for creating and saving invoices. I'm actually not even sure she's using any of them from a .xlt file vs just reusing a .xls file. As a matter of fact, two of the options actually initially required that she click a button to get a new invoice number, the other (3rd) had a sheet to use a template for new invoice sheets with blank cell where invoice number is to go, and when that sheet was copied to new sheet, then the invoice number was placed into the new sheet with the source sheet keeping a blank cell there for future reference: This 3rd version that works by assigning invoice number to newly created sheets from the one has this code at the Worksheet level: Private Sub Worksheet_Activate() If ActiveSheet.Name = "Invoice" Then Exit Sub ' do not put number on basic template sheet End If If IsEmpty(Range(InvoiceNumberLocation)) Then Range(InvoiceNumberLocation) = IncrementInvoiceNumber End If End Sub Where "Invoice" is the sheet name of the 'template' sheet; InvoiceNumberLocation is the cell where the invoice number is to be placed; and IncrementInvoiceNumber is actually a function that determines what the next number is to be. So, Rule #1 - don't put number on the template invoice sheet, and Rule #2 - don't overwrite an existing invoice number already on a sheet. "Bob Phillips" wrote: You obviously know a lot more about the situation than me, but whichever course they take, my advice still holds good does it not? The post was asking how not to take the code with the template. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Bob - to explain. First must determine which of 3 possible workbooks she's talking about. One was developed in a long thread elsewhere and uses an external .txt file to contain the last invoice number used. Two others use a Name to keep the number internal to the file. Earlier versions had the code updating the invoice number entry every time the file was opened - that was from original code obtained from another site. I've modified the code in that one, and set up code in the two other versions to never insert an invoice number into a cell with an invoice number already in it. Consider the version with the external .txt file tracking last used invoice number: you open the template file, it pops the next invoice number into the proper cell. You open that same file again, the code will not do that because it will see there is already an invoice number in it. In the case of the one with the invoice number kept internally, there is a "template" sheet that has no entry in the invoice number cell - the code in the sheet's Activate event tries to put a number in it UNLESS: there's already a number in it, OR if the sheet name is the template sheet's name. There is a button on the sheet to create new invoice which simply copies the 'template' sheet which in turn activates the new sheet, which automatically assigns that new sheet the next invoice number in sequence. All that's left to be decided, as far as I know, is which one of the two PammiJ feels will work best with the way they do the work at their establishment. "Bob Phillips" wrote: Have your code in a master workbook, with an invoice template worksheet within it. Then copy that template to a new workbook, like so ThisWorkbook.Worksheets("Template").Copy With ActiveWorkbook 'your code End With just make sure that all of the code works on the actoive workbook from that point on. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Pammi J" wrote in message ... Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
From Template Save As Help
J; I have'nt seen her template but if I understand her problem just add an IF
statement (If(customer name="",perform your procedure,). Is this an over simplification? Lou "JLatham" wrote: PammiJ, Once I get the answers to email I sent in response to yours this morning (which may be waiting at home now), I'll be working back in that to get the whole thing set up the way you folks there need it to be so that it operates the way you need. "Pammi J" wrote: Hi i have a invoice template and with the help of JLatham i have managed to get the invoice number to increment by one each time i open it. BUT i have a big problem when i have filled out the invoice with customer details and the invoice number iss example 100 and then `save as` (cust name and no) it seems to be carrying the macro with it - so when i open cust name inv 100 it becomes 101 - and then i open template that becomes 102 and so on. I need it to save as per the village software invoice - saves just a copy to wherever i specify so if i need to reopen that copy it doesnt change the invoice number. Any help much appriciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with a template | Excel Discussion (Misc queries) | |||
Can I save a re-useable template for Text Import Wizard? | Excel Discussion (Misc queries) | |||
Save as Msg box | Excel Discussion (Misc queries) | |||
How and where to save template? | Excel Discussion (Misc queries) | |||
Template in Excel | New Users to Excel |