Monday, February 27, 2012

Generating Reports from a Gradebook

Back in November, the following comment was left for me:
I have a question, though. The current gradebook has one sheet for the dashboard, and it is possible to flick through the various students one by one. What about if I wanted to be able to see the dashboards for many students? I like the idea of having a single dashboard so that I can keep customizing the report as needed, but is there a way of then exporting this to generate 30 individual sheets for the different students in my class? In other words, hit PRINT once instead of 30 times...

Fabulous question, of course. I didn't know the answer to it at the time, but have had it on my "to do" list. Then, Debra Dalgleish shared this fabulous post last week about how to Filter Excel Data on Multiple Sheets. Although the information wasn't exactly what I was needing, it showed me that finding an answer for the commenter was possible. I just had to push a little further. A little Googling led me to this post on Using Excel 2007 for Progress Tracking in the Classroom. Huzzah! The perfect model to poach.

I copied the VBA from the Progress Tracking post into my Beginner's Gradebook, and then changed it only enough to suit my own named cells/ranges. Now, I will tell you that what I know about VBA would fit in a thimble. I'm eager to learn, but my lessons are currently being driven by "What do I need to know right now?" rather than any sort of organized manner. If you have some expertise you are willing to share to tweak the code to make things work more smoothly, I welcome any and all suggestions.

Here's how things are currently set up. In the "Scores" worksheet, I have added a data validation list (in grey box) that has the last names of the students. This cell was assigned a name. There is also a "Run Reports" button associated with the VBA shown below. In the "Report" worksheet, I had to make a couple of changes. First, I removed the data validation list associated with the last name. Why the data validation works in the "Scores" worksheet, but not the "Report" worksheet when the VBA runs is a mystery to me. Beyond that, I had to name the cell in the report with the last name, and use a formula to make it equal to what I named the cell with the new data validation list.

I'll put the VBA  code below, in case anyone has any suggestions. With very few exceptions, it is identical to the one in the Progress Tracking post by Danny Khen---definitely not my work. But for now, feel free to download the workbook and give things a try. It is a macro-enabled workbook, so be sure to tell Excel it's okay to play with. When you click on the "Run Report" button, a new workbook will open and populate with a separate report for every student, all on their own worksheets. All of the data will be in their places with bright shiny faces. It's magic! (Where's Doug Henning when you need him?) All you need to do is tell Excel to print the entire workbook and you're good to go.


13 comments:

  1. Thanks! I've been looking forward to this reply. I'll check it out and see how it works...

    ReplyDelete
  2. This post is truly exciting for most, but a little sad for me. Our district runs almost everything off Macs. When I tried to check the VBA out, it didn't run because VBA's don't work in Office '08 for Mac. :( I'm weeping on the inside. I've spent a TON of time modeling my proficiency-based grade book on your amazing excel spreadsheets. Just yesterday I ran reports for all my students. It took a long time to run each report individually and I wanted this code. Now it's there and I can't use it.

    ANY IDEAS???

    Thank you for all you do to help teachers everywhere!

    ReplyDelete
  3. Hey, Windle,

    I know less about Macs than I do VBA. :( However, some poking around on "Teh Googles" suggests that something called "AppleScript" can be used in place of VBA. Perhaps someone in your district has mad skillz with AppleScript and could convert the VBA in the post for you?

    Other than that, I'd recommend storming the district office and demanding Office 2011 for Mac. The updated addition brings back VBA for Apple OS.

    Glad my posts have been useful for you...in spite of the coding issue. Hope you'll post some suggestions for improvements!

    ReplyDelete
    Replies
    1. It's taken me awhile to get back to this idea. Newborns will do that to ya :) Is there an easy way to convert my modified grade book so that it will accept the VBA? I'm planning on installing Office 2011 over the summer, but I'd like to print reports before then. I think the easiest way to do that is to sacrifice some of the formatting when I convert from excel for mac to PC. That way I can use the script from the post above. How would I go about installing the script? I know next to nothing about VBA.

      Thanks, as always!

      Delete
  4. Hi, Windle---congrats on the new baby! To use VBA in Excel, you will need to enable the "Developer" Tab in the ribbon (if it isn't already). Go into "Options," then "Customize Ribbon" and check the box next to "Developer."

    In your workbook, create the data validation list and then use the "Design Mode" button the Developer tab to create the "Run Reports" button. Finally, on the Developer tab, choose "VBA Editor" on the far left. This will open a new dialogue box where you can paste in the code. Save it and you should be golden.

    Here is a link to a text file with the VBA code, or you can copy and paste from my workbook.

    ReplyDelete
    Replies
    1. You are A-MAZ-ING!!! Thank you so much!

      Delete
    2. Hi again Science Goddess,

      I'm back for more fun with SBG. Our district was supposed to have a new student information system this year, but like a lot of things that cost money, that didn't happen :(

      I have a couple questions for you since I'll be using this grade book again this year.

      1. Is there an easy way to rename sheets without messing up the formulas on the report?
      2. This is a BIG one. Is there a way to use the VBA script with more than one class in the grade book? If not, I think I'm going to have to make separate grade books for every period and make sure ALL the formulas, list, sheets, etc. are named exactly the same as yours are :)

      As always, thank you in advance!

      ~Windle

      Delete
  5. I've been poking around with this for a week or so, but my VBA skills are not good enough to take this on. But there are some possible workarounds. One is simply what you noted and just keep each book separate.

    The other way to do it would be to copy the report sheet within a workbook---one for each class. Then, you could copy the VBA module and rename the variables for each class/report.

    I'll keep an eye out for "one VBA code to rule them all." It's on my list to learn.

    ReplyDelete
  6. Any chance you still have the original spreadsheet handy?? It now longer seems to be attached to the Microsoft blog web page but perhaps I am missing something... I did see this a couple years ago and downloaded it then but have since misplaced it :(

    ReplyDelete
  7. I do have it. If you'll email me directly (the_science_goddess [at] yahoo [dot] com, I'll send it to you.

    ReplyDelete
  8. Hiya!

    First off, thanks so much for all of your great advice on this blog. It's been an absolute life saver. I've been tooling around with this VBA and I've come across one problem. The VBA has code that will delete the formulas from the reports after the Macro is run, but is there a way to delete the formulas in charts? Most of the information in my report is in the form of charts but the charts end up just being populated with whatever student is currently selected in the Index. Any tips to get this chart data to show up properly in the reports?

    ReplyDelete
  9. This is amazing. I've encountered a problem though. After adding more students by adding rows the code stopped working. It only generates the first report. Any ideas?

    ReplyDelete
  10. Any chance you have an updated code that works for Google spreadsheets?

    ReplyDelete