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,
Microsoft Office 2007 Professional Plus Key, 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,
Microsoft Office Home And Business, 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,
office 2010 standard key, 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,
Cheap windows 7 Home Premium, I want to point out a great primer that Sam Radakovitz wrote a few months back on Dynamic Ribbon ##################. 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
Tools and Utilities for the 2007 Microsoft Office System
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: "
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:
After you install the CustomUIeditor, open the attached "MediaPlayer.xlsm" to see the CustomUI.xml:
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="" onLoad="Mediaplayer.xlsm!OnRibbonLoad">
<ribbon startFromScratch="true">
<tabs>
<tab id="settingsTab" label="Media Player">
<group idMso="GroupTheme######cel" 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,
Microsoft Office Professional 2007 Key, 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="GroupTheme######cel" 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. <div
office 2010 standard key Building the Excel Media
Linear Mode
