Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Invoice
Hello all..
I am currently using an invoice template in excel and inputting the info directly onto the invoice in excel. How would one create a user interface that would ask for the desired information and once completed would print the invoice including totals/costs etc without displaying the excel UI. I hope this makes some sense. I am brand new so I am not sure of the terminology or if this can be done. This will be used to speed up inputing information and creating customer invoices. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Invoice
You probably cannot achieve exactly what you want. Excel requires that at
least one worksheet be visible in the book. But you could use what is known as a User Form to get the input from the user and that form would transfer the information to a worksheet and cause it to be printed. But setting up a user form is going to take some coding on your part to get it to do all that you want. Why is it that you do not want people entering information directly onto the invoice sheet? If it is because you are worried that they may erase something or overwrite a formula then you can protect from that by using Tools | Protection | Protect sheet and give it a password that you write down and keep safe somewhere. By default all cells on a worksheet are locked initially, but I'm betting that if you got the template off of the web somewhere, that the cells you need to enter information into are probably unlocked. To find out, try this. Use Tools | Protection | Protect Sheet and just hit enter, no password for now. Then try to enter some information somewhere that you would expect the user to do so. If you cannot enter it then, the whole sheet is probably locked. If you can enter data in those places, then check one of the other cells, simply choose it and try to type into it. If it is locked, it won't let you and you get an error message. If it accepts your typing, you can use Edit | Undo or the undo icon to get the previous contents back. If you want to start from scratch and set up the cell protection so that the user can only type into cells you want them to use, start with an unprotected sheet. If it's locked from the previous test, use Tools | Protection | Unprotect Sheet. Choose all of the cells on the sheet (easy way is to click the empty gray square right above the '1' for Row 1 and to the left of the 'A' for column A. All cells will be selected. Then use Format | Cells and go to the [Protection] tab. Make sure that the check box next to "Locked" is solid black - may have to click it a couple of times to get it solid, not gray or empty. That means all cells will be locked when you Protect the sheet again. Close the window and go back to the sheet. Select the cells, one by one, or in groups by clicking one and then holding down the [ctrl] key while clicking others to be unlocked. If there are a lot, I find it easier to select a few and then protect them, then get another group and do the same. Either way, while those cells that you want the user to be able to type into are selected, again choose Format | Cells and on the [Protection] tab, clear that checkbox next to "Locked". Now when you apply Tools | Protection | Protect Sheet, the user will only be able to type into the cells you've said they could. Be sure and test it to make sure it works the way you want. "Mr C" wrote: Hello all.. I am currently using an invoice template in excel and inputting the info directly onto the invoice in excel. How would one create a user interface that would ask for the desired information and once completed would print the invoice including totals/costs etc without displaying the excel UI. I hope this makes some sense. I am brand new so I am not sure of the terminology or if this can be done. This will be used to speed up inputing information and creating customer invoices. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Invoice
Thank you for taking the time to help.
Actually I do not mind the excel showning I just thought I was missing something. I have just begun doing some tutorials. Your information has helped a lot. Once again thank you very much "JLatham" wrote: You probably cannot achieve exactly what you want. Excel requires that at least one worksheet be visible in the book. But you could use what is known as a User Form to get the input from the user and that form would transfer the information to a worksheet and cause it to be printed. But setting up a user form is going to take some coding on your part to get it to do all that you want. Why is it that you do not want people entering information directly onto the invoice sheet? If it is because you are worried that they may erase something or overwrite a formula then you can protect from that by using Tools | Protection | Protect sheet and give it a password that you write down and keep safe somewhere. By default all cells on a worksheet are locked initially, but I'm betting that if you got the template off of the web somewhere, that the cells you need to enter information into are probably unlocked. To find out, try this. Use Tools | Protection | Protect Sheet and just hit enter, no password for now. Then try to enter some information somewhere that you would expect the user to do so. If you cannot enter it then, the whole sheet is probably locked. If you can enter data in those places, then check one of the other cells, simply choose it and try to type into it. If it is locked, it won't let you and you get an error message. If it accepts your typing, you can use Edit | Undo or the undo icon to get the previous contents back. If you want to start from scratch and set up the cell protection so that the user can only type into cells you want them to use, start with an unprotected sheet. If it's locked from the previous test, use Tools | Protection | Unprotect Sheet. Choose all of the cells on the sheet (easy way is to click the empty gray square right above the '1' for Row 1 and to the left of the 'A' for column A. All cells will be selected. Then use Format | Cells and go to the [Protection] tab. Make sure that the check box next to "Locked" is solid black - may have to click it a couple of times to get it solid, not gray or empty. That means all cells will be locked when you Protect the sheet again. Close the window and go back to the sheet. Select the cells, one by one, or in groups by clicking one and then holding down the [ctrl] key while clicking others to be unlocked. If there are a lot, I find it easier to select a few and then protect them, then get another group and do the same. Either way, while those cells that you want the user to be able to type into are selected, again choose Format | Cells and on the [Protection] tab, clear that checkbox next to "Locked". Now when you apply Tools | Protection | Protect Sheet, the user will only be able to type into the cells you've said they could. Be sure and test it to make sure it works the way you want. "Mr C" wrote: Hello all.. I am currently using an invoice template in excel and inputting the info directly onto the invoice in excel. How would one create a user interface that would ask for the desired information and once completed would print the invoice including totals/costs etc without displaying the excel UI. I hope this makes some sense. I am brand new so I am not sure of the terminology or if this can be done. This will be used to speed up inputing information and creating customer invoices. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Invoice
Don't get the idea that I think user forms are a bad thing. Just a little
more effort needed to implement them, and they do offer some distinct advantages at times. We do one invoice on a contract we have that involves about 80 employees and everything going to/coming from the cost tracking sheets for doing things like adding a new employee to the contract or altering certain 'fixed' information like job category, task assignments and billing rates is done through user forms which validates entries before they are passed on to the sheets. Only thing the payroll accountant has to do is enter bi-weekly hours worked, and information about travel or materials expenses (through forms also) and then click a "build invoice" button and it's all done without hardly ever touching a worksheet. This has turned what was previously a troublesome workbook into a very stable and reliable tool. "Mr C" wrote: Thank you for taking the time to help. Actually I do not mind the excel showning I just thought I was missing something. I have just begun doing some tutorials. Your information has helped a lot. Once again thank you very much "JLatham" wrote: You probably cannot achieve exactly what you want. Excel requires that at least one worksheet be visible in the book. But you could use what is known as a User Form to get the input from the user and that form would transfer the information to a worksheet and cause it to be printed. But setting up a user form is going to take some coding on your part to get it to do all that you want. Why is it that you do not want people entering information directly onto the invoice sheet? If it is because you are worried that they may erase something or overwrite a formula then you can protect from that by using Tools | Protection | Protect sheet and give it a password that you write down and keep safe somewhere. By default all cells on a worksheet are locked initially, but I'm betting that if you got the template off of the web somewhere, that the cells you need to enter information into are probably unlocked. To find out, try this. Use Tools | Protection | Protect Sheet and just hit enter, no password for now. Then try to enter some information somewhere that you would expect the user to do so. If you cannot enter it then, the whole sheet is probably locked. If you can enter data in those places, then check one of the other cells, simply choose it and try to type into it. If it is locked, it won't let you and you get an error message. If it accepts your typing, you can use Edit | Undo or the undo icon to get the previous contents back. If you want to start from scratch and set up the cell protection so that the user can only type into cells you want them to use, start with an unprotected sheet. If it's locked from the previous test, use Tools | Protection | Unprotect Sheet. Choose all of the cells on the sheet (easy way is to click the empty gray square right above the '1' for Row 1 and to the left of the 'A' for column A. All cells will be selected. Then use Format | Cells and go to the [Protection] tab. Make sure that the check box next to "Locked" is solid black - may have to click it a couple of times to get it solid, not gray or empty. That means all cells will be locked when you Protect the sheet again. Close the window and go back to the sheet. Select the cells, one by one, or in groups by clicking one and then holding down the [ctrl] key while clicking others to be unlocked. If there are a lot, I find it easier to select a few and then protect them, then get another group and do the same. Either way, while those cells that you want the user to be able to type into are selected, again choose Format | Cells and on the [Protection] tab, clear that checkbox next to "Locked". Now when you apply Tools | Protection | Protect Sheet, the user will only be able to type into the cells you've said they could. Be sure and test it to make sure it works the way you want. "Mr C" wrote: Hello all.. I am currently using an invoice template in excel and inputting the info directly onto the invoice in excel. How would one create a user interface that would ask for the desired information and once completed would print the invoice including totals/costs etc without displaying the excel UI. I hope this makes some sense. I am brand new so I am not sure of the terminology or if this can be done. This will be used to speed up inputing information and creating customer invoices. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sequential invoice numbering | Excel Worksheet Functions | |||
Linking to a pivot table from a formatted invoice | Links and Linking in Excel | |||
missing invoice toolbar when save customised invoice | New Users to Excel | |||
Master invoice number | Excel Worksheet Functions | |||
Increment an Invoice number | Excel Worksheet Functions |