ExcelEndeavor
New Member
- Joined
- Oct 13, 2020
- Messages
- 30
- Office Version
- 365
- Platform
- MacOS
Quick Background:
I have a budget workbook with 7 different worksheets - one for each "Business Line" (department). Budget items are added through a userform that currently allows the user to add up to 3 invoices when they input a purchase order number. The details of those invoices (number, date, amount) are captured in specific columns in each worksheet, depending on which business line is selected in the userform. I am now learning from my AP dept that there can be up to 14 invoices for a single purchase order, so I am looking for ways to organize the information.
This screenshot shows the userform that is used to input the budget info. The red highlighted area is where invoices can be added and the info is transferred to one of the worksheets, depending on which Business Unit is selected.
On the worksheets, the invoice information is mapped to the columns shown when the "Submit" button is clicked in the userform.
The mapping code of the submit button in the userform shows the invoice data going to:
The Question (two fold):
1. What is the best way to organize this? If I create more columns to accomodate the other 11 possible invoice fields, it would result in 3 columns per invoice (number, date, amount) - a grand total of 42 columns (all of which would be hidden from view - they would only exist for oranization. I would have to also figure out how to have the new invoice details map to the next available set of 3 columns (number, date, amount). Or would a different method work more efficiently?
2. How do I code this input method?
These are just userform mockups that I created - they are not coded:
The one on the left shows how the invoice portion would look (in red). By clicking the add invoice, another userform would open (the right side screenshot) where the invoice details would be input. Once the invoices has been added, it would show in the listbox of the original userform (highlighted in red) and keep a running list of invoices. Lastly, the invoice would only count mathematically if the "Paid" checkbox has been clicked.
I know this is a ton of information all at once, but what do I do and where do I start? Thank you in advance.
Here is the file if you want to explore...
Budget
I have a budget workbook with 7 different worksheets - one for each "Business Line" (department). Budget items are added through a userform that currently allows the user to add up to 3 invoices when they input a purchase order number. The details of those invoices (number, date, amount) are captured in specific columns in each worksheet, depending on which business line is selected in the userform. I am now learning from my AP dept that there can be up to 14 invoices for a single purchase order, so I am looking for ways to organize the information.
This screenshot shows the userform that is used to input the budget info. The red highlighted area is where invoices can be added and the info is transferred to one of the worksheets, depending on which Business Unit is selected.
On the worksheets, the invoice information is mapped to the columns shown when the "Submit" button is clicked in the userform.
The mapping code of the submit button in the userform shows the invoice data going to:
VBA Code:
rSh.Range("I" & nextRow).Value = Invoice1Nbr
rSh.Range("J" & nextRow).Value = Invoice1Date
rSh.Range("K" & nextRow).Value = Invoice1Amnt
rSh.Range("L" & nextRow).Value = Invoice2Nbr
rSh.Range("M" & nextRow).Value = Invoice2Date
rSh.Range("N" & nextRow).Value = Invoice2Amnt
rSh.Range("O" & nextRow).Value = Invoice3Nbr
rSh.Range("P" & nextRow).Value = Invoice3Date
rSh.Range("Q" & nextRow).Value = Invoice3Amnt
The Question (two fold):
1. What is the best way to organize this? If I create more columns to accomodate the other 11 possible invoice fields, it would result in 3 columns per invoice (number, date, amount) - a grand total of 42 columns (all of which would be hidden from view - they would only exist for oranization. I would have to also figure out how to have the new invoice details map to the next available set of 3 columns (number, date, amount). Or would a different method work more efficiently?
2. How do I code this input method?
These are just userform mockups that I created - they are not coded:
The one on the left shows how the invoice portion would look (in red). By clicking the add invoice, another userform would open (the right side screenshot) where the invoice details would be input. Once the invoices has been added, it would show in the listbox of the original userform (highlighted in red) and keep a running list of invoices. Lastly, the invoice would only count mathematically if the "Paid" checkbox has been clicked.
I know this is a ton of information all at once, but what do I do and where do I start? Thank you in advance.
Here is the file if you want to explore...
Budget