Hello there!
I am having trouble with my macro. Actually, it's not surprising, given I have very, very limited knowledge in VGA (to avoid saying non-existant).
My XLS file is here.
I use Office 2003.
The first sheet of my XLS file is barely a calculation sheet based on what I ought to be spending or earning in this or that category. The second sheet is the more interesting one, with a list of transactions. The third sheet would be supposed to show the accounts' balances depending on the transactions inputed in sheet 2.
I can configurate sheet 3 quite easily, it's not the main problem. No, what I was encountering problems with was to make a macro which would fill in the chart semi-automatically.
When launching the macro, a box would pop-up and appear as the one I "drew" in the form "NewTransaction". The user would input various information and on his hitting "add it", a new line would be created in the chart, with as value in the cells the data that the user has put in the box.
The various information would be :
* the text written in text box under "description of transaction" would logically be outputed as such in the column "description" of our new line in the chart on Sheet 2.
* The three combo boxes under the "date" would allow to select a day in the first, a month (in letters) in the second, and a year (in yyyy format) in the third, and would output the complete date as a dd/mm/yy format in the "date column" of the new line in the chart on sheet 2
* the combo box under the payment method label would comprise : "Cash", "Check", "Visacard", and would each output the correspounding word into the value of the cell on column "payment method" of the new line created in the chart
* The "Option button" selected between the two proposed would output the correspunding text into the column "account" of the line.
* The three combo-boxes under the category thing would be the toughest. The first one would allow do chose between "expense" and "Receipt". Once this choice made, the second combo-box would allow to chose between the different descriptions of expenses or receipts seen on sheet 1 (i.e. if we chose "receipt" in the first combo-box, the second one will allow you to chose between "grant and scholarships", "parental income", "work income" and "student loans"). The third box would subsequently let you chose between the different sub-sub-categories of receipts or expenses, depending on which you chose in the second box.
* The whole of these 3 boxes would output only the name of third box into the coumn "Category" of our new line
* But the whold information would be used to display "no" or "yes" in the "within bounds" column of our line, depending on whether the total amount of expenses in, for example, Clubbing (category punctual leisure, in expenses) has been exceeded for the current month, thus adding the amounts of transactions of the same month and the same category to see whether it's still within bounds, and displaying "no" in big fat red letter in case it's not.
* the text box "tr #" would ideally work itself out depending on the previous Transaction numbers, as the plan would be to store them all. So the text in the box beside the label Tr# should already contain you transaction number when the box appears.
* Naturally, the numbers entered in the "amount" textbox would be output into the "acount" column of the new line, in POUNDS (the euro cell is only there to read and all works should be made in GBP)
* finally, but this as I sais I would be able to figure out, transactions ought to be reported in the "account balance", using the "which account" column to know in which chart to put the transaction, and using the first combo-box of the "categories" to know whether to put in the "credit" or "debit" column
* So ideally, when you would fill in a form for a new transaction, the account balancing would settle itself.
by no mean pay attention to the euros cell. It is only a quick conversion to that which is above it, using a variable called "pound" and which I set on sheet 1.
I would completely understand your not having the time or willingness to bother to pay attention to this macro as you never asked for it and therefore I would be boundlessly grateful if you do.
I am having trouble with my macro. Actually, it's not surprising, given I have very, very limited knowledge in VGA (to avoid saying non-existant).
My XLS file is here.
I use Office 2003.
The first sheet of my XLS file is barely a calculation sheet based on what I ought to be spending or earning in this or that category. The second sheet is the more interesting one, with a list of transactions. The third sheet would be supposed to show the accounts' balances depending on the transactions inputed in sheet 2.
I can configurate sheet 3 quite easily, it's not the main problem. No, what I was encountering problems with was to make a macro which would fill in the chart semi-automatically.
When launching the macro, a box would pop-up and appear as the one I "drew" in the form "NewTransaction". The user would input various information and on his hitting "add it", a new line would be created in the chart, with as value in the cells the data that the user has put in the box.
The various information would be :
* the text written in text box under "description of transaction" would logically be outputed as such in the column "description" of our new line in the chart on Sheet 2.
* The three combo boxes under the "date" would allow to select a day in the first, a month (in letters) in the second, and a year (in yyyy format) in the third, and would output the complete date as a dd/mm/yy format in the "date column" of the new line in the chart on sheet 2
* the combo box under the payment method label would comprise : "Cash", "Check", "Visacard", and would each output the correspounding word into the value of the cell on column "payment method" of the new line created in the chart
* The "Option button" selected between the two proposed would output the correspunding text into the column "account" of the line.
* The three combo-boxes under the category thing would be the toughest. The first one would allow do chose between "expense" and "Receipt". Once this choice made, the second combo-box would allow to chose between the different descriptions of expenses or receipts seen on sheet 1 (i.e. if we chose "receipt" in the first combo-box, the second one will allow you to chose between "grant and scholarships", "parental income", "work income" and "student loans"). The third box would subsequently let you chose between the different sub-sub-categories of receipts or expenses, depending on which you chose in the second box.
* The whole of these 3 boxes would output only the name of third box into the coumn "Category" of our new line
* But the whold information would be used to display "no" or "yes" in the "within bounds" column of our line, depending on whether the total amount of expenses in, for example, Clubbing (category punctual leisure, in expenses) has been exceeded for the current month, thus adding the amounts of transactions of the same month and the same category to see whether it's still within bounds, and displaying "no" in big fat red letter in case it's not.
* the text box "tr #" would ideally work itself out depending on the previous Transaction numbers, as the plan would be to store them all. So the text in the box beside the label Tr# should already contain you transaction number when the box appears.
* Naturally, the numbers entered in the "amount" textbox would be output into the "acount" column of the new line, in POUNDS (the euro cell is only there to read and all works should be made in GBP)
* finally, but this as I sais I would be able to figure out, transactions ought to be reported in the "account balance", using the "which account" column to know in which chart to put the transaction, and using the first combo-box of the "categories" to know whether to put in the "credit" or "debit" column
* So ideally, when you would fill in a form for a new transaction, the account balancing would settle itself.
by no mean pay attention to the euros cell. It is only a quick conversion to that which is above it, using a variable called "pound" and which I set on sheet 1.
I would completely understand your not having the time or willingness to bother to pay attention to this macro as you never asked for it and therefore I would be boundlessly grateful if you do.
