Selected Answer
The code in the attached workbook is contained in two modules as follows.
- A standard code module TeachExcel and
- A UserForm Selector. Note that you need to right-click on this module and select either View Code or View Object, to either see the code or the form.
To somewhat familiarise yourself with the project, please start by reading all the comments in the code, especially where it explains what you can modify. Please make sure you fully understand how to make modifications. Now is the time to ask :-)
Next, test the code in the attached workbook with a view to ensure that it is indeed possible to specify the subreport you would like to create. Just click the green button on the lone Dashboard tab. The program will ask you to specify a file which must be similar in structure to the one you published with your question. It will then read the contents of that file into the drop-downs of the user form that opens. Once you click OK the program ends because it has reached the end of the scope of this thread. If the specified data source was open before it will remain open. Otherwise it will be closed.
The form changes when you select PO Date from the first drop-down. Instead of a second drop-down to select a filter criterium you get two text boxes asking for dates.. This is where failure might loom. The program will reject entries which aren't recognised as dates. Your PC can recognise dates as specified in your Regional Settings. It doesn't matter which recognisable format you enter, the form will always convert it to "dd mmm yyyy". That doesn't mean it's the preferred input format. VBA couldn't care less how hard it is on you or how easy. You should use the most convenient one.
Normally, the Short Date format is the easiest to enter, consisting of numbers only. The Short Date in your settings is actually what used to be the Medium Date format requiring numbers and characters and therefore more elaborate. I think the form will accept "d-m-[yy]" despite your settings, inserting the current year if omitted, the important criterium being the date separator. Try it and let me know if it gives you a headache.
Once this portion of your project is approved please ask your next question based on both the attached workbook and a copy of your sample data. Make sure that the sample data are structured and are of the exact type as the data you finally will need to work with. Attach both files to the question and specify what you want done. I honestly don't know because your initial post wasn't clear enough on that point. Now it will be easier to focus.
Take a moment to consider methodology. As you realise I planned to keep the code separate from the data (at variance from what I had first thought). The data file will be opened, read and closed - never modified in any way. On second thought I intended to create the report in the workbook that contain the code but now I plan on creating a third workbook for the output. Do you think that will be good?
Note that the code makes changes to the workbook it is in and then tells Excel to forget all changes. This has the effect that you will not be reminded to save changes if you close ThisWorkbook after running the program. You may like to write some instructions to the user on the Dashboard or make changes to the code that you wish to keep. After you do, don't rely on Excel to remind you to save the changes. The advantage in writing output to a third workbook will be that you can always close ThisWorkbook without saving, thereby avoiding accidental modifications of the code.
