Eradicator
New Member
- Joined
- Feb 20, 2003
- Messages
- 2
Specific Questions follow at the end of this post that can be addressed independantly. Please reply. This is an urgent request with time not on my side.
Thanks!
Background:
I am a Tax Man (Auditor) by profession and am the team leader on a project where huge export companies are being inspected for compliance with export regulations. I streamlined the process to the following:
I prepared a spreadsheet that is printed which has a space for the Date of the Sale, The Invoice Number, and then it lists the various methods of export and the related specific requirements of the export regulations as per export method.
For instance:
Method1: Road.
Requirements: Invoice, CustomsDocs, ProofOfDelivery, ProofOfPayment.
Method2: Rail.
Requirements: Invoice, TransitOrder, ProofOfPayment.
The Date, Invoice, Method and Requirements fields are followed by a space big enough for the tax payer to put down his signature. There is space for 10 invoices on a page. This sheet is printed and consulted in the investigation.
A red pen is used to supply the date and invoice number, and to select the method of export and to tick the requirements that were met. This is repeated for each and every export in the period under review. When the 10th invoice was dealt with, the sheet is handed to the tax payer for signature. This takes several days to complete.
The next step is for me to start working through those pages and pages of red-pen-marked schedules, so as to present my findings in an orderly fashion to a Technical Committee charged with the responsibility of making final decisions on the tax payers’ compliance with the regulations and ultimately his fate. You can appreciate the effort that is pumped into an exercise like that, to get it ready and presentable for decision-making.
So I thought about it for a second longer and realised I can design a program in VB that will do all the work for me, so that I can simply sit there with my laptop and in stead of working with a red pen and paper, I could feed the required info into the system, have that schedule printed and signed by the guy like I would have before, but then have the system automatically do the sorting and presentation of the collected data automatically.
Problem is, I am very new at programming and don’t know how to export data to an excel spreadsheet yet. Besides, we are 5 people on this project, so doing it in excel will make it simpler to distribute the final product in the form of an excel workbook than it would be to distribute a program with our security measures in place.
My Design and Thinking:
In all there are 8 methods and roughly an average of 6 requirements each that may be the same for a few, as you could see from the example above.
My complete workbook will contain roughly 20 sheets. On the first sheet (called Intro) the user will enter the company's name and tax reference number.
The second sheet is basically the collecting sheet that collects the information that would have been on the printed sheet, ticked in red. This is called Work.
The remaining sheets are based on the method of exports, named after the method of export. It would list the data collected, sorted first per period, then by the number of requirements actually met.
These sheets mentioned above should, upon completion of the actual inspection, be able to be exported to a new workbook named after the name of the tax payer, so that I would have a collection of workbooks for the different companies I audited. These workbooks will only have the sorted data sheets and should be printer friendly from the start. And of course, should be able to be changed by the initial excel thingy in the event where the scope of the audit may be increased to include more months of exports.
The data from the array (refer to Work Done) is to be added to different lists on each sheet: All Requirements Met, 5 Requirements Met, … , No requirements Met. The data added to these lists should then be sorted in date order.
Also to be supplied to the top of these sheets, are the details of the tax payer that were supplied on the text boxes on the first sheet.
These sheets should only come into existence when there is such a method of export used. For instance, the final workbook for a company that only has exports by road should only have one sheet in it.
Work Done So Far:
The First Sheet, called INTRO:
It’s here that info is collected on the tax payer’s name and tax number and the period under review with the use of text boxes (txtName, txtVATNumber and txtPeriod). TxtName is supposed to be used later when you export those sheets to a new workbook with the tax payer’s name. TxtName is also to be used on the sheets to differentiate the printed sheets from one another. But that still comes.
The second sheet, the collecting sheet, called WORK:
Firstly, with the use of option boxes, you select the method of export, which is one option out of 8. Secondly, once an option is selected, the tick boxes for the requirements become available (enabled). The requirements met are then simply marked with a tick. Thirdly the date, invoice number, and the amount of the invoice is supplied in text boxes. Once these three text boxes were filled, a command button (NEXT) becomes available, and clicking on it, the data collected is passed to an array called Export(InvoiceNumber,0-18). The tickboxes are cleared, a default method of export is selected and the sheet is ready to receive the next series of data. InvoiceNumber = (InvoiceNumber + 1). This is repeated for all the exports until the user presses a command button called STOP.
Finally, my questions:
1.) How do I select the sheet depending on the method of export? If the method is by road, and I want to select the data to be posted to the sheet called “Road”, how do I do that?
2.) How do I get the tax payer details onto a specific cell on those sheets? Lets say I want txtName to appear in H13 of every sheet (other than the sheets called Intro and Work), what is the procedure to be added to “Private Sub txtName_Change()”? Is it even a procedure to insert here? Or is there a reference I can make directly from within the relevant cell to the txtName.Text string?
3.) How do I make sure the data passed to the “Road” sheet, is added to only one of the 6 lists available, and that it is added to the correct list? Remember, the data will be passed to a specific sheet, in a specific list that is determined by method of export, and the number of requirements met.
4.) Once I determined which sheet and list the data should go to, how do I determine the exact spot the data will go if it is determined by the value of the invoice date (being in the format of mmyy)?
5.) If I determined that the data should go in between two rows of data already there, how do you insert a space for the data to go?
6.) How is data transferred from the array to the cells in the list on the relevant sheet?
7.) How do I create a copy of a template sheet only when that method of transport/export is selected? Remember, I want all the sheets to look the same, but will only create a sheet once that specific method of export is used.
8.) How do I create a new workbook and copy only a selected number of sheets from the current workbook there? I want to be able to control the destination of the file also.
9.) How do you enable changes to be made to the above-mentioned workbook if you later need to change anything on it?
10) How can I then protect the completed work so that when I distribute it to the
team members, that it is not accidentally altered or anything?
Well, that was a mouth and plate full. Hope it is challenging enough and not too boring.
Please let me know if I should email you a copy of what I’ve done so far so that you can have a graphical representation of what I meant here.
This message was edited by Eradicator on 2003-02-22 03:10
Thanks!
Background:
I am a Tax Man (Auditor) by profession and am the team leader on a project where huge export companies are being inspected for compliance with export regulations. I streamlined the process to the following:
I prepared a spreadsheet that is printed which has a space for the Date of the Sale, The Invoice Number, and then it lists the various methods of export and the related specific requirements of the export regulations as per export method.
For instance:
Method1: Road.
Requirements: Invoice, CustomsDocs, ProofOfDelivery, ProofOfPayment.
Method2: Rail.
Requirements: Invoice, TransitOrder, ProofOfPayment.
The Date, Invoice, Method and Requirements fields are followed by a space big enough for the tax payer to put down his signature. There is space for 10 invoices on a page. This sheet is printed and consulted in the investigation.
A red pen is used to supply the date and invoice number, and to select the method of export and to tick the requirements that were met. This is repeated for each and every export in the period under review. When the 10th invoice was dealt with, the sheet is handed to the tax payer for signature. This takes several days to complete.
The next step is for me to start working through those pages and pages of red-pen-marked schedules, so as to present my findings in an orderly fashion to a Technical Committee charged with the responsibility of making final decisions on the tax payers’ compliance with the regulations and ultimately his fate. You can appreciate the effort that is pumped into an exercise like that, to get it ready and presentable for decision-making.
So I thought about it for a second longer and realised I can design a program in VB that will do all the work for me, so that I can simply sit there with my laptop and in stead of working with a red pen and paper, I could feed the required info into the system, have that schedule printed and signed by the guy like I would have before, but then have the system automatically do the sorting and presentation of the collected data automatically.
Problem is, I am very new at programming and don’t know how to export data to an excel spreadsheet yet. Besides, we are 5 people on this project, so doing it in excel will make it simpler to distribute the final product in the form of an excel workbook than it would be to distribute a program with our security measures in place.
My Design and Thinking:
In all there are 8 methods and roughly an average of 6 requirements each that may be the same for a few, as you could see from the example above.
My complete workbook will contain roughly 20 sheets. On the first sheet (called Intro) the user will enter the company's name and tax reference number.
The second sheet is basically the collecting sheet that collects the information that would have been on the printed sheet, ticked in red. This is called Work.
The remaining sheets are based on the method of exports, named after the method of export. It would list the data collected, sorted first per period, then by the number of requirements actually met.
These sheets mentioned above should, upon completion of the actual inspection, be able to be exported to a new workbook named after the name of the tax payer, so that I would have a collection of workbooks for the different companies I audited. These workbooks will only have the sorted data sheets and should be printer friendly from the start. And of course, should be able to be changed by the initial excel thingy in the event where the scope of the audit may be increased to include more months of exports.
The data from the array (refer to Work Done) is to be added to different lists on each sheet: All Requirements Met, 5 Requirements Met, … , No requirements Met. The data added to these lists should then be sorted in date order.
Also to be supplied to the top of these sheets, are the details of the tax payer that were supplied on the text boxes on the first sheet.
These sheets should only come into existence when there is such a method of export used. For instance, the final workbook for a company that only has exports by road should only have one sheet in it.
Work Done So Far:
The First Sheet, called INTRO:
It’s here that info is collected on the tax payer’s name and tax number and the period under review with the use of text boxes (txtName, txtVATNumber and txtPeriod). TxtName is supposed to be used later when you export those sheets to a new workbook with the tax payer’s name. TxtName is also to be used on the sheets to differentiate the printed sheets from one another. But that still comes.
The second sheet, the collecting sheet, called WORK:
Firstly, with the use of option boxes, you select the method of export, which is one option out of 8. Secondly, once an option is selected, the tick boxes for the requirements become available (enabled). The requirements met are then simply marked with a tick. Thirdly the date, invoice number, and the amount of the invoice is supplied in text boxes. Once these three text boxes were filled, a command button (NEXT) becomes available, and clicking on it, the data collected is passed to an array called Export(InvoiceNumber,0-18). The tickboxes are cleared, a default method of export is selected and the sheet is ready to receive the next series of data. InvoiceNumber = (InvoiceNumber + 1). This is repeated for all the exports until the user presses a command button called STOP.
Finally, my questions:
1.) How do I select the sheet depending on the method of export? If the method is by road, and I want to select the data to be posted to the sheet called “Road”, how do I do that?
2.) How do I get the tax payer details onto a specific cell on those sheets? Lets say I want txtName to appear in H13 of every sheet (other than the sheets called Intro and Work), what is the procedure to be added to “Private Sub txtName_Change()”? Is it even a procedure to insert here? Or is there a reference I can make directly from within the relevant cell to the txtName.Text string?
3.) How do I make sure the data passed to the “Road” sheet, is added to only one of the 6 lists available, and that it is added to the correct list? Remember, the data will be passed to a specific sheet, in a specific list that is determined by method of export, and the number of requirements met.
4.) Once I determined which sheet and list the data should go to, how do I determine the exact spot the data will go if it is determined by the value of the invoice date (being in the format of mmyy)?
5.) If I determined that the data should go in between two rows of data already there, how do you insert a space for the data to go?
6.) How is data transferred from the array to the cells in the list on the relevant sheet?
7.) How do I create a copy of a template sheet only when that method of transport/export is selected? Remember, I want all the sheets to look the same, but will only create a sheet once that specific method of export is used.
8.) How do I create a new workbook and copy only a selected number of sheets from the current workbook there? I want to be able to control the destination of the file also.
9.) How do you enable changes to be made to the above-mentioned workbook if you later need to change anything on it?
10) How can I then protect the completed work so that when I distribute it to the
team members, that it is not accidentally altered or anything?
Well, that was a mouth and plate full. Hope it is challenging enough and not too boring.
Please let me know if I should email you a copy of what I’ve done so far so that you can have a graphical representation of what I meant here.
This message was edited by Eradicator on 2003-02-22 03:10
