Windows 7 Starter Building the Excel Media Player

Today's author, Jon Adams, a Tester on the Excel team. A copy of the spreadsheet discussed in this post can be found as an attachment at the bottom of this post.
As you are probably well aware, Excel is a very versatile tool and is often used in ways that are hard to imagine. This blog post is a follow up of Joe's post "Unusual (but cool!) Uses of Excel" . I am going to show you how to use Excel for rapid application development using the new Office Art graphics Engine, ActiveX, Table Objects,Office 2010 Standard Key, VBA (Excel OM and ActiveX), and a touch of custom Ribbon code. The complete solution description will be split up over several posts, with the following topics covered in each:
Part 1 Building UI using Office Graphics
Part 2 Using ActiveX Controls Table Manipulation Data Analysis
Part 3 Context Menus Ribbon UI
Since I am an avid music geek and spend way too much time organizing my media library, our sample project is going to be a fully functional Media Player.
Before I get started I will answer a question that I am sure is burning in your mind. Why in the world would I write such an application in Excel?
Flexibility: I can quickly create mash-ups of my music library data with web service data with a few more clicks and determine things like 'listening habits' and do pivots on things like artists playing in the area. Best of all, this can all be done outside of a development environment and without compiling.
Office Graphics: First off, as a tester, I wanted to create something that exercised as much of the Office Graphics engine that was kind of fun. Secondly, the new office graphics engine is much more professional looking and provides the end user with a lot more flexibility right out of the box. I wanted to come up with an interesting solution that showcased the power of the new graphics engine.
Now let's get started. Download the file (link at the bottom of this post), play with it and get a feel for how it works. You will have to enable macros in order for the application to work.Building UI using Office Graphics
Excel 2007 completely changed what is possible with graphics on the grid, and I will show you some of the features you can use to make an elegant solution. First let's break apart this UI.
Choose a background and frame your UI: The background can be as a simple as a grey box to frame some controls or in this case I decided to use a picture. If you are building a tool for your company, you can use your corporate logo as a watermark. After the size is set by your frame, you can clean things up by holding Alt and dragging resizing to line up with the grid. For a crisper UI, I like to hide the rest of the rows. Select the first column header after your picture and press ctrl + right to select the remaining columns. Repeat for rows. This gives you something like this:
If you wish to hide the column/row headers you can go to Excel Options (Alt + T, O) and toggle "Show row and column headers".
Let's create the side-pane now. This simply consists of 5 rectangles grouped and a series of shapes for the playback controls.
To make this step easy I suggest using the new Selection Pane (Home, Find and Select,Office Pro Plus 2007, Selection Pane). This will help you to create deep grouped objects, and assign them useful names for later in the tutorial.
Now add a few shapes to the grid. On the insert tab, shapes dropdown,Office Home And Business 2010 Key, you have a choice of many shapes for our UI. I added 6 rectangles, an equal sign and a few chevron symbols. I also went ahead and typed a few simple titles in the rectangles and was able to get this.
If you multi-select the objects (hold down CTRL while you click the objects you want to select), you can right click and group them to make it a little easier to move them together. Now in the selection pane you can name your groups and your objects for better organization.
I renamed all of my objects in the tree view and grouped them for easier use later and it looks like this in the selection pane.
Notice the visibility toggles. When building UI it is quite useful to be able to just hide an entire group while working on something else.
Ok, now let's make this look a little better with some snazzy effects. Some effects work on the group as a whole, such as reflection and glow,Windows 7 Starter, whereas others, such as bevel and some scene3d effects, will apply to each object individually. Let's start by selecting an object and clicking the format tab. Let's try adding a group effect. From the Shape Effects dropdown add a glow.
The Format Tab gives us a lot of options, and maybe some canned preset will work but let's dive deeper. Press Ctrl + 1 to bring up the advanced formatting dialog. In the 3-D format tab, Top Bevel, the first bevel creates a very button like illusion. For a more metallic look change lighting to flood and material to metal. Now onto the fill tab; If you add a gradient it will be applied to the entire group. I also like to use transparency to create a more vibrant UI.
After a bit of time these are a few options I end up with. This should give you enough of an overview on Office Graphics formatting.
Now let's add some functionality. All of our objects link to either hyperlinks or macros. Right click one of the rectangles and clicking Hyperlink allows you to link to another sheet in the document. My Library,Microsoft Office 2010 Professional, Playlist, Discover and Analyze UI are all on separate sheets. You can also right click each object and assign a macro (more on that later).
That's it for now. Stay Tuned for the next blog post where we will dive into how to use ActiveX controls and Tables in excel to build the remaining components of a Media Player in Excel. <div