Power Utility Pak v7

Add-in tools for Microsoft Excel 2007

About PUP v7

PUP v7 includes most of the features available in PUP v6, with a few additions and enhancements. Some of the PUP v6 features have been removed because they are no longer necessary if you use Excel 2007.

This document describes what is new and different in PUP v7, relative to PUP v6.

New User Interface

PUP v7 uses the new Excel 2007 "tab-and-ribbon" user interface. When PUP v7 is installed, you see a new tab: PUP v7. Click the tab and the PUP ribbon is displayed. The figure below shows a preliminary version of the PUP v7 ribbon (click for a larger view). The layout and icons may change in the final release.

The PUP utilities have also been re-arranged to correspond to the controls on the ribbon. I think they are now easier to find on the ribbon than they were in a menu.

New File Format

All of the PUP files use the new Excel 2007 Open Office file format. The primary advantage is significantly smaller file size. For example, the PUP v7 download is about 40% the size of the PUP v6 download.

New Features

The new features in PUP v7 are described below.

View Active Workbook's Folder
This simple, but very useful utility, opens an Explorer window that shows the files in the active workbook's directory.
Insert A Live Calendar
Inserts a small calendar into an 8-row by 7-column range. The dates are calculated using formulas, so you can enter a new month and year in the top cell and the calendar updates automatically.
Object Report
Creates a report that describes all graphic objects on a worksheet.
Sudoku Puzzle Generator
A perfect way to avoid doing real work.

Enhanced Features

Following is a list of the PUP v7 features that have been enhanced.

Currency Conversion
The currency rates are obtained from a different (and hopefully more reliable) source.
Conditional Formatting Report
The report has been modified so it's a sortable table. It also includes the new Excel 2007 conditional formatting options.
Error Condition Wizard
This utility includes an option to create the formula using the new IFERROR function
Exact Formula Copy
This utility now handles a range that contains a mixture of array formulas and non-array formulas.
Copy Page Setup
This utility has been completely rewritten, and now copies header/footer images.
Select By Format
Added a new option to select cells based on their style.
Enhanced Shortcut Menus
The Cell shortcut menu now includes an option to toggle the page break display -- something that normally requires a lengthy trip to the Excel Options dialog box.
Perpetual Calendar
I removed the picture/bitmap options in "paste as picture" because they have no effect in Excel 2007. The choices now are: color, black and white, and black and white with transparent background. The calendar picture is rendered using the active workbook's theme colors and font. I also removed the option to create an appointment calendar. It was never happy with that feature, and I don't think anyone actually used it.
3D Cell Shading
When applying 3D formatting to a range, you now have the option of not applying a background color.
Filename Lister
When importing a list of files, you can now specify that the list include the files in all subdirectories.
Alternate Row Shading
The color choices reflect the colors from the active workbook's document theme.
Create Workbook Contents Sheet
If you choose the option that adds buttons and macros, you'll see a warning if the workbook isn't in a macro-enabled file format.
Calculator
The pop-up calculator uses colors based on the active workbook's document theme. Why? Because it can.
PUP Worksheet Functions
Added three new functions: (1) CELLCOLOR (returns the color value of a cell's background color in decimal or hex). (2) FONTCOLOR (returns the color value of a cell's font color in decimal or hex). (3) SAYIT (vocalizes the argument).
PUP Help
The PUP Help file uses a modified CSS style sheet, so it has a much cleaner look and resembles the Help files used by Office 2007.

Changed Features

Following is a list of features that have changed, because of restrictions in Excel 2007.

Date Picker Toolbar
The Date Picker is now a "stay-on-top" dialog box rather than a floating toolbar. However, it has the same functionality as the toolbar version. In fact, it has more functionality: you can choose to enter the date with formatting, the date only, or the formatting only.
PUP InfoBox
The former InfoBox toolbar is a "stay-on-top" dialog box in PUP 2007. In PUP v6, this feature displayed information about the active cell or the currently selected range. In PUP v7, it displays information only about the active cell. I spent the better part of a day trying to figure out why monitoring the current range selection consistently crashed Excel. I eventually gave up. The dialog box is also resizable.
Worksheet Map
When you create a worksheet map, you'll get a floating dialog box instead of a floating toolbar. This dialog box contains buttons to navigate between the map and the worksheet.
PUP Bookmarks
In the past, the PUP Bookmarks feature was implemented using a toolbar. In PUP V7, the bookmarks appear in a pop-up menu. When you invoke it with Ctrl+Shift+B, the menu appears at the current cell. In addition, you can now create a bookmark using a cell or range name rather than the address.
Unapply Names
Unfortunately, this utility is now less function than it the past. It will not run if your workbook contains identical names (for example, two identical sheet-level names on different sheets). Working with identical names was far too error-prone.
Export Range As Graphic
I removed the picture/bitmap option because it has no effect in Excel 2007. In addition, I added a note that exported ranges must have a background color (even if it's white). Otherwise, the image is terrible (another Excel 2007 quirk).
Toggle Settings
The Toggle Settings dialog box no longer has an option to show/hide the Task Pane. It does, however, have an option to show/hide the Selection and Visibility pane.
Convert Chart to Picture
The grayscale option is no longer available (because it looked terrible).
Interactive Hide / Unhide
Made the workbook and sheet listboxes wider.
Compare Ranges
Made the sheet selection listboxes wider.

Removed Features

Following is a list of features that were present in PUP v6, but have been removed from PUP v7.

Create a PUP Toolbar
Support for custom toolbars in Excel 2007 is extremely limited.
Chart Shortcut Menus
Excel 2007 no longer supports custom shortcut menus for charts, so if you choose the PUP Enhanced Shortcut menu options, right-clicking a chart will have no new menu items.
Toggle FaceID Toolbar
This feature, intended for Excel developers, is no longer useful in Excel 2007.
Interactive Zoom
Zooming a worksheet is now a simple matter, using the Zoom controls in the Excel status bar.
Object Hide/Unhide
Excel's new Selection and Visibility pane makes this utility obsolete.
Insert Symbol
Excel's Insert Symbol tool is much better.
Add Path to Header or Footer
This utility is obsolete because Excel has this feature.
PUP Worksheet Functions
Removed two functions because they are not relevant for Excel 2007: CELLCOLORINDEX and CELLFONTCOLOR.