jgoods
04-15-2015, 07:44 PM
The Excel file contains data on a company’s outstanding accounts from different customers. Each row corresponds to a particular customer. It indicates the size of the customer (1 for Small, 2 for Medium, 3 for Large), the transaction date in 2013, the number of shirts ordered and the amount of the payment due. Develop a user form that has the usual OK and Cancel buttons, two sets of option buttons and a list box. The first set of option buttons allows the user to choose the size of the customer (using captions Small, Medium and Large), and the second set allows the user to choose the Number of shirts purchased or the Amount column to summarize. The list box lists the twelve months of the year and allows the user to select one or more months to be summarized. Then write code in a sub called summarize placed in a module that takes the choices made in the form and displays a message listing the appropriate information and average number or amount.
The sub summarize should be called from a button placed on the worksheet with the data. The sub will call the form as described above. The form should be called over and over until the cancel button on the form is clicked and the options selected in the form should be preserved between calls to the form. The form should have your full name in the title bar. Also the message boxes will have your full name in the title bar. The average for days should be reported with a number with two significant digits and the average for amount should be displayed as currency with two significant digits. The following illustrates the screen captures that you will need to submit. (To capture the images press <alt-PrtSrc> and to paste them in your document press <Ctrl-V>).
Note that the program should work for any range that starts in cell A4 and that the orders and dates do not have to be sorted by size for the program to work. Try to size your images so that they fit on one page printed on both sides. Bring your program in a flash drive to class on Thursday so that you can run it with different data.
(Hints: You can define dim strMth as Variant and then have an statement like strMth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") to populate an array with the months. The VBA function Month(date) returns a number between 1 and 12 for the month of January,…, December, respectively. Your code in the sub Summarize will have to go through each line of data and count and accumulate either the number or the amount for the selected size and the selected months. For the month, your code would have to cycle through all the selected months and see if the transaction date occurred in one of the selected months.)
All I have so far is
Sub Summarize()
With UserForm1.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = ""
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
UserForm1.Show
End Sub
I created the user form and have all of the options on it. I just don't know how to get it to calculate an average based on the options selected.
The sub summarize should be called from a button placed on the worksheet with the data. The sub will call the form as described above. The form should be called over and over until the cancel button on the form is clicked and the options selected in the form should be preserved between calls to the form. The form should have your full name in the title bar. Also the message boxes will have your full name in the title bar. The average for days should be reported with a number with two significant digits and the average for amount should be displayed as currency with two significant digits. The following illustrates the screen captures that you will need to submit. (To capture the images press <alt-PrtSrc> and to paste them in your document press <Ctrl-V>).
Note that the program should work for any range that starts in cell A4 and that the orders and dates do not have to be sorted by size for the program to work. Try to size your images so that they fit on one page printed on both sides. Bring your program in a flash drive to class on Thursday so that you can run it with different data.
(Hints: You can define dim strMth as Variant and then have an statement like strMth = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") to populate an array with the months. The VBA function Month(date) returns a number between 1 and 12 for the month of January,…, December, respectively. Your code in the sub Summarize will have to go through each line of data and count and accumulate either the number or the amount for the selected size and the selected months. For the month, your code would have to cycle through all the selected months and see if the transaction date occurred in one of the selected months.)
All I have so far is
Sub Summarize()
With UserForm1.ListBox1
.MultiSelect = fmMultiSelectMulti
.RowSource = ""
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
End With
UserForm1.Show
End Sub
I created the user form and have all of the options on it. I just don't know how to get it to calculate an average based on the options selected.