Back
Excel

Building the Excel Media Player – Part 3

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/>
     <button id=Monitor label=MonitorFolders onAction=Mediaplayer.xlsm!MonitorFolders/>
    </group>
    <group id=libraryTab label=Library/Playlists>
     <button id=ShowPlaylist label=Goto Playlists onAction=Mediaplayer.xlsm!ShowPlaylists/>
     <button id=ShowCurPlaylist label=Goto Now Playing onAction=Mediaplayer.xlsm!ShowNowPlaying/>
     <button id=Lib label=Goto Library onAction=Mediaplayer.xlsm!GotoLibrary/>
     <button id=SufflePL label=Shuffle Current Playlist onAction=Mediaplayer.xlsm!mixPlaylist/>
    </group>
    <group id=qPL label=Quick Playlists>
     <button id=QuickPlaylist label=Create Quick Playlist onAction=Mediaplayer.xlsm!QuickPL/>
    </group>
    <group id=controls label=Controls>
     <button id=Play label=Play onAction=Mediaplayer.xlsm!PlayR/>
     <button id=Pause label=Pause onAction=Mediaplayer.xlsm!PauseR/>
     <button id=Stop label=Stop onAction=Mediaplayer.xlsm!StopR/>
     <button id=Next label=Next onAction=Mediaplayer.xlsm!NextR/>
     <button id=Previous label=Previous onAction=Mediaplayer.xlsm!PreviousR/>
     <button id=Open label=Open onAction=Mediaplayer.xlsm!OpenR/>
     <button id=Shuffle label=Shuffle onAction=Mediaplayer.xlsm!SuffleR/>
    </group>
   </tab
  </tabs>
 </ribbon>
</customUI>

If you are creating a new solution, the sample tab will get you started with a few interesting cases. Look at the portal above to learn more about what you can do. The menu that this represents is:

Let’s first look at startFromScratch=true. If this is set to true, the original RibbonUI will not be accessible or visible. Contextual tabs will continue to show unless you explicitly set those tabs ‘Visible=False’. In the above image ‘StartFromScratch’ is true, but I removed this in the sample so that it is easy to use the rest of Excel UI.

Next in the tabs groups, I wanted to make the Media Player follow the Themes available in Office 2007 to give it a ‘skinnable’ feel. To do this I added a preexisting group on my custom Media Player Tab: <group idMso=GroupThemesExcel visible=true/> If you wish to repurpose, reuse, or add buttons to existing Ribbon objects, you can find information on the MSO ControlIDs here.

Next there are a few custom groups, and in each of the groups you will set of buttons with an OnAction property set. When you click the ‘generate callbacks’ button in the Custom UI Editor, you will get auto generated VBA code. All I did is add a call to Play_Click so that my ribbon functionality matches my Office Graphics button functionality.

‘Callback for Play onAction
Sub xlsm!PlayR(control as IRibbonControl) 
    Play_Click
End Sub

As mentioned above, if you are doing a lot of add-in development and Ribbon additions, I recommend investigating Visual Studio Tools for Office.

And there you have it, the basic concepts to creating a fully functional media library/player in Excel. As mentioned in the first post, the great thing about creating solutions in Excel is the flexibility it provides. For instance, if you import a concert event RSS feed into a table, you can easily do a VLookup on the artist name and create playlists based on the music in the area. You can then link to a few social web services (Last.FM or Zune for instance) to find ‘related’ artists to your most listened to artists with minimal effort. With Excel, anything is possible.