Restore Stoplights on Spreadsheet

Project Center Stoplight Views provide convenient at-a-glance illustrations of the status of various project indicators, such as whether the project is on-time, on-budget, or is meeting expected milestones.

The intended recipients of these stoplight views are often managers or CEO’s, who do not regularly log in to Project Server. For this reason, Project Server allows for Project Center Views to be exported to Excel, using the “Export Grid to Excel” function.

Unfortunately, when you use the “Export Grid to Excel” function, the stoplight images are not preserved in the Excel document. The manager or CEO then is unable to scan the stoplight images to determine quickly how projects in the portfolio are progressing.

Here is what your spreadsheet looks like after using the “Export Grid to Excel Function”:

 

Figure 1. Please Click Image Above for Larger View.

 

What you’d like to do is restore the stoplights from the Project Center view, so that they appear in the exported Excel spreadsheet, like this:

 

Figure 2. Please Click Image for Larger View

The problem is that only the text values of the fields in the Project Center View are displayed instead of the graphical image stoplights you have assigned to the different field values.

By selecting the appropriate field values in the VBA code of the spreadsheet, the correct stoplight image can be identified and inserted.

In case you are not familiar with using VBA code in an Excel spreadsheet, you open the Visual Basic Editor by hitting the [Alt+F11] keys, or by navigating to Tools on the main menu, selecting Macro, and then selecting Visual Basic Editor. Figure 3 shows how the menu option would appear:

 

Figure 3. The Visual Basic Editor Selected from the Excel Menus.

You can download the sample spreadsheets, code, and stoplight images, so that you can follow along in the tutorial.

If you haven’t done so, in the Visual Basic Editor, go to the left-hand tree structure labeled VBA Project and click on Module1. You should see code that looks like this:

 

Figure 4. VBA Code Snippet – Beginning of ResetStoplights() code.

This code sets up some of the variables used, turns off changes to the video screen while processing, sets up a status bar message, and then allows the title “Project Center” to be added to the top of the spreadsheet in Column C.

In Figure 5 below, we see the heart of the code to restore the stoplights. Here, the cell indicating the top and left position of the first Excel row and column with data is selected. In this instance, it is F6, and not really a row with data on it, but it represents a logical place to start processing the spreadsheet.

 

Figure 5. Insert Stoplights in Place of Text Values.

When looking at the exported spreadsheet, we can see that there are three different text values that exist in place of our desired stoplights. The first is the value, “On Schedule “. In our Project Center view, “On Schedule ” was represented by a green stoplight. Similarly, the value “Minor Issues” was represented by a yellow stoplight, and “Serious Issues ” was represented by a red stoplight.

The way to restore the original stoplights is to examine each cell in the spreadsheet, and based on its text value, replace the text with the appropriate stoplight image. In the code above the If statement checks for the presence of one of the three text values, and then uses the subroutine “InsertPicture” to place the stoplight image into the cell.

The InsertPicture VBA code is described at ExcelTip.com, in the article: Insert pictures using VBA in Microsoft Excel.

The next lines of VBA code determine how many rows there are in the Excel spreadsheet to be processed. The way the code is designed is that it will march down each column, evaluating the cell for the presence of one of our three text values described above, until the last row is processed. Here is the code for that process:

 

Figure 6. Process Each Row of Data

You can assign a button, such as the one pictured here, to your ResetStoplights() code and be able to convert the text fields into stoplights in just moments.

 

Figure 7. Convert Button Assigned to ResetStoplights() subroutine.

Note that if you missed the link above, you can download the Restore Stoplights on Excel Spreadsheets code with this link. Included in the download are the Excel Spreadsheets, with the spreadsheets in their “before” conversion state, and “after” conversion state. See above to learn how to access the code.

So, the “before” spreadsheet will not have the stoplights present until you press the convert button. Please note that the stoplight images are also included in the download. If you do not want to edit the path to the images from within the code, just place all three images in your “C:\” root directory.

That bears repeating. If you do not see the stoplight images after pressing the “Convert” button, make sure that you have dropped the images in your “C:\” directory. The paths to the image should be:

  • “C:\green_stoplight.jpg”
  • “C:\yellow_stoplight.jpg”
  • “C:\red_stoplight.jpg”

Finally, in the last section of code, the spreadsheet is setup for printing:

 

Figure 8. End Processing of Stoplights Code

The call is made to format the spreadsheet for printing by the call to the SetupForPrint subroutine, pictured below:

 

Figure 9. Setup Spreadsheet for Printing

This sets the page orientation to “Landscape”, and sets the parameters for how to fit the spreadsheet onto the printed the pages. You will need to adjust the properties, “.FitToPagesWide” and “.FitToPagesTall” to whatever is appropriate for you.

Leave a Reply

Your email address will not be published. Required fields are marked *