Adding Context and Ribbon Menus¶
xlSlim allows you to easily create right-click context menus and ribbon bar menus. The functionality has been designed to be as simple as possible, no knowledge of Custom UI XML is required!
We will show how to add a plotting menu to simplify adding line and column plots of selected data.
We will add this right-click context menu:
And this ribbon bar menu:
Clicking on the menu items will generate plots of the selected data:
However the menu functionality itself does not require a premium license, so Python functions executing within the bundled Python environment do not need premium license.
We will use these two Python functions to create the plots from the selected data:
import pythoncom import win32com.client as win32 XLSLIM_COMAPPFUNC = None def AddLinePlot(): excel_obj = XLSLIM_COMAPPFUNC() app = excel_obj.Application selection = app.Selection active_sheet = app.ActiveSheet # xlLine = 4 chart = active_sheet.Shapes.AddChart2(-1, 4) chart.Chart.SetSourceData(Source=selection) def AddClusteredColumnPlot(): excel_obj = XLSLIM_COMAPPFUNC() app = excel_obj.Application selection = app.Selection active_sheet = app.ActiveSheet # xlColumnClustered = 51 chart = active_sheet.Shapes.AddChart2(-1, 51) chart.Chart.SetSourceData(Source=selection)
We start by opening a new Excel workbook and pasting the Python code into cell A1:
Then we use
CreatePyModule() to create a dynamic Python module and register the two functions:
We use the
UpdateContextMenu() to add the functions AddLinePlot() and AddClusteredColumnPlot() to the right-click context menu.
We start by adding just AddLinePlot() to a menu called Plotting with one menu item Line.
The menu is updated as expected:
Selecting data and clicking on the PlottingLine menu item generates plots as expected:
Adding further menu items is done by extending the range passed into
At this stage our context menu looks like this:
We’d like to add some images to make the menu look more professional. The image for the top level menu is an optional third argument to
UpdateContextMenu() and the images for the menu items are an optional third column in the range passed as the second argument.
The available images are restricted to those built into Excel. Fortunately there are many builtin images to choose from. Available images can be browsed at images. These look like good images for the menu items:
And this looks like a good candidate for the top menu:
We can amend the
UpdateContextMenu() call to include these image names:
Now the menu has nice images:
The same menu is now available on the Excel ribbon:
All the Python code and Excel files shown are available from github in the xlslim-code-samples repo. I highly recommend downloading the samples from github. The Excel workbooks contain many tips and tricks.