Browse by Tags

Last post we talked about how to use Excel as a flexible development environment and touched on how to use ActiveX Controls to enhance your solution and using tables programmatically to manipulate data. In this post we are going to drill into how to add custom Ribbon UI, context menus. Custom Context Menu In order to make playlist creation a lot easier in the Excel Media Player, I added the following custom right click context options to my Tables. To see how to do this open the MediaPlayer.xlsm, press Alt + F11 and double click ThisWorkbook. You will see the following code in the Workbook_Open() event: AddMenuItem "Add Selected Song", "AddSelected" AddMenuItem "Add Selected Artist", "AddArtist" AddMenuItem "Add Selected Album", "AddAlbum" AddMenuItem "Add Current Filter", "AddFilter" And Addmenu item this is defined below as: Private Sub AddMenuItem(title As String, command As String) Dim x As CommandBarControl Set x = Application.CommandBars("List Range Popup").Controls.Add(1, temporary:=True) x.Caption = title x.OnAction = command x.Visible = True End Sub CommnadBars("List Range Popup") is the bar for right click context menu for Lists/Tables. For cells, you can use CommandBars("Cells") instead. Using Status Bar and Application.ScreenUpdating When you are doing a lot of things in the background programmatically such as bringing in a lot of data (such as a media library) or modifying lots of charts, or anything that is time intensive, you probably want to do a combination of 3 things: Update status with info Turn off screen updating DoEvents Update Status: Excel's status bar can be written to by setting Application.StatusBar, so if you are in a loop it is useful to do something like Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count so that the end user will know approximately the time remaining for your operation. One thing to note, if you don't set Application.StatusBar = "" the status bar will keep your last value until you exit the application (not just the workbook) or something else writes to the status bar. Turn off Screen updating If you are moving around lots of data and the user doesn't need to see what is going on set Application.ScreenUpdating = "False" before your code and Application.ScreenUpdating = "True" after your code completes. With this setting you will gain a slight performance increase and the user will not see flickering and data moving do to your code changes. DoEvents If, on the other hand, you want to allow the user to continue to use the app while the code is running (ie, if you have an animated object or a long loop) then you can sprinkle DoEvents into your loops or methods, for Example: While Range("isRunning").Value DoEvents Animate Wend Where I define Animate to modify a few objects. Finish up with custom Ribbon UI Before I go into detail on this topic, I want to point out a great primer that Sam Radakovitz wrote a few months back on Dynamic Ribbon Galleries . This is a great primer on how to customize the ribbons as are the links he provides are relevant to this post as well: "Office Fluent Ribbon Developer Portal http://msdn2.microsoft.com/en-us/office/aa905530.aspx Tools and Utilities for the 2007 Microsoft Office System http://msdn2.microsoft.com/en-us/office/aa905356.aspx The RibbonX we will create will live in the document and travel with it. We will have to use a tool to get the RibbonX in the document. The one I'll use for this example is called the "Office 2007 Custom UI Editor". You can download it freely from here: http://openxmldeveloper.org/articles/CustomUIeditor.aspx " Custom UI Editor is a very light weight tool for giving you a few suggestions, validating CustomUI XML and generating sample VBA callback code. If you are going to be doing add-in development or extensive Ribbon UI customization, I highly recommend using VSTO 2008 instead of this tool. You can find info here: http://msdn2.microsoft.com/en-us/library/d2tx7z6d.aspx After you install the CustomUIeditor, open the attached "MediaPlayer.xlsm" to see the CustomUI.xml: < ?xml version = " 1.0 " encoding = " utf-8 " ?> < customUI xmlns = " http://schemas.microsoft.com/office/2006/01/customui " onLoad = " Mediaplayer.xlsm!OnRibbonLoad " > < ribbon startFromScratch = " true " > < tabs > < tab id = " settingsTab " label = " Media Player " > < group idMso = " GroupThemesExcel " visible = " true " /> < group id = " LibrarySettings " label = " Library " > < button id = " GetLibrary " label = " Get Library From WMP " onAction = " Mediaplayer.xlsm!GetFromWMP " image = " bones " Read...

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, 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, 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, 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, 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...