{"id":622,"date":"2018-11-13T18:37:27","date_gmt":"2018-11-13T18:37:27","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=622"},"modified":"2023-01-05T10:05:20","modified_gmt":"2023-01-05T10:05:20","slug":"how-to-customize-excel-context-menus","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/how-to-customize-excel-context-menus\/","title":{"rendered":"Customising Excel’s Context Menus"},"content":{"rendered":"\n
  1. Introduction<\/a><\/li>
  2. Adding a Button to the Cells Menu<\/a><\/li>
  3. Dynamic Menus<\/a><\/li>
  4. Other Menu Controls<\/a><\/li>
  5. Resources<\/a><\/li><\/ol>\n\n\n\n

    Introduction<\/h2>\n\n\n\n

    Note:<\/strong> This tutorial requires PyXLL, the Python Excel Add-In<\/a>, >= 4.0 and Excel >= 2010.<\/em><\/p>\n\n\n\n

    Context, or shortcut, menus in Excel are one of the most commonly used user interface features of Excel. The most well know context menu is the cells menu. When you right click on a worksheet cell or selection you’re presented with a menu with a range of options for copy and pasting, cell formatting and other functions.<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

    This post shows how to customize these context menus with using PyXLL, the Python Excel Add-In<\/a>, in versions of Excel from 2010 onward (including Office 365).<\/p>\n\n\n\n

    PyXLL<\/a> embeds Python in Excel, allowing you to write fully-featured Excel add-ins and replace VBA with Python.<\/p>\n\n\n\n

    Adding a Button to the Cells Menu<\/h2>\n\n\n\n

    Context menus are customized using the ribbon xml file specified in the pyxll.cfg config file. See Customizing the Ribbon<\/a> in the PyXLL user guide for more details about configuring the ribbon.xml file.<\/p>\n\n\n\n

    If you’ve not already installed PyXLL, see the Introduction<\/a> to get started.<\/p>\n\n\n\n

    Note:<\/strong> Customizing the context menus using the ribbon.xml file is only possible in Excel 2010 and later versions (including Office 365). For that reason, the following will not work with Excel 2007 or earlier versions.<\/em><\/p>\n\n\n\n

    The xml below shows how to add a button and a sub-menu containing some more buttons to the Excel Cells context menu. Copy and paste this code and save it to a file named ribbon.xml<\/em> and add it to your pyxll.cfg file as follows:<\/p>\n\n\n

    \n[PYXLL]\nribbon = &lt;path to ribbon.xml>\n<\/pre><\/div>\n\n
    \n<?xml version="1.0" encoding="UTF-8"?>\n<customUI xmlns="http:\/\/schemas.microsoft.com\/office\/2009\/07\/customui">\n    <ribbon>\n        <!-- The ribbon and context menus are specified in the same file -->\n    <\/ribbon>\n    <contextMenus>\n        <contextMenu idMso="ContextMenuCell">\n            <button id="MyButton" label="Toggle Case Upper\/Lower\/Proper"\n                insertBeforeMso="Cut"\n                onAction="context_menus.toggle_case"\n                imageMso="HappyFace"\/>\n            <menu id="MySubMenu" label="Case Menu" insertBeforeMso="Cut">\n                <button id="Menu1Button1" label="Upper Case"\n                    imageMso="U"\n                    onAction="context_menus.toupper"\/>\n                <button id="Menu1Button2" label="Lower Case"\n                    imageMso="L"\n                    onAction="context_menus.tolower"\/>\n                <button id="Menu1Button3" label="Proper Case"\n                    imageMso="P"\n                    onAction="context_menus.toproper"\/>\n            <\/menu>\n        <\/contextMenu>\n   <\/contextMenus>\n<\/customUI>\n<\/pre><\/div>\n\n\n
    Note:<\/strong> The “http:\/\/schemas.microsoft.com\/office\/2009\/07\/customui” schema is important, and is different from the schema used in the example ribbon.xml file found in the PYXLL examples. Without this your context menus won’t appear.<\/em><\/p>\n\n\n\n
    Once you’ve configured PyXLL with this ribbon.xml file, when you reload PyXLL or restart Excel you’ll see those menus added to the cells context menu.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    If you try selecting any of the newly added menu items, nothing happens! That’s because the Python functions that the menu items reference haven’t been written yet.<\/p>\n\n\n\n
    On each menu item you’ll see an onAction<\/em> tag. The value of those is the Python function that should be called when the menu item is selected. We need a new module called “context_menus.py” where we can write those functions. The module name isn’t important as long as it matches the xml, but we’ve used “context_menus” in the xml above.<\/p>\n\n\n
    \nfrom pyxll import xl_app\n \n \ndef toggle_case(control):\n    \"\"\"Toggle the case of the currently selected cells\"\"\"\n    # get the Excel Application object\n    xl = xl_app()\n \n    # iterate over the currently selected cells\n    for cell in xl.Selection:\n        # get the cell value\n        value = cell.Value\n \n        # skip any cells that don't contain text\n        if not isinstance(value, str):\n            continue\n \n        # toggle between upper, lower and proper case\n        if value.isupper():\n            value = value.lower()\n        elif value.islower():\n            value = value.title()\n        else:\n            value = value.upper()\n \n        # set the modified value on the cell\n        cell.Value = value\n \n \ndef tolower(control):\n    \"\"\"Set the currently selected cells to lower case\"\"\"\n    # get the Excel Application object\n    xl = xl_app()\n \n    # iterate over the currently selected cells\n    for cell in xl.Selection:\n        # get the cell value\n        value = cell.Value\n \n        # skip any cells that don't contain text\n        if not isinstance(value, str):\n            continue\n \n        cell.Value = value.lower()\n \ndef toupper(control):\n    \"\"\"Set the currently selected cells to upper case\"\"\"\n    # get the Excel Application object\n    xl = xl_app()\n \n    # iterate over the currently selected cells\n    for cell in xl.Selection:\n        # get the cell value\n        value = cell.Value\n \n        # skip any cells that don't contain text\n        if not isinstance(value, str):\n            continue\n \n        cell.Value = value.upper()\n \n \ndef toproper(control):\n    \"\"\"Set the currently selected cells to 'proper' case\"\"\"\n    # get the Excel Application object\n    xl = xl_app()\n \n    # iterate over the currently selected cells\n    for cell in xl.Selection:\n        # get the cell value\n        value = cell.Value\n \n        # skip any cells that don't contain text\n        if not isinstance(value, str):\n            continue\n \n        cell.Value = value.title()\n<\/pre><\/div>\n\n\n
    The “Excel.Application” object returned by “pyxll.xl_app()” is exactly the same as the Application object in VBA. It’s part of the Excel Object Model, and is exactly the same in Python as it is in VBA. Therefore, you can use it to automate Excel and do everything that you would previously have done using VBA.<\/p>\n\n\n\n
    Make sure this new module is on the Python path in your pyxll.cfg file, and add it to the list of modules.<\/p>\n\n\n
    \n[PYXLL]\nribbon = ribbon.xml\nmodules =\n    context_menus\n \n[PYTHON]\npythonpath =\n    &lt;path to your Python code>\n<\/pre><\/div>\n\n\n
    \"\"<\/figure>\n\n\n\n
    Dynamic Menus<\/h2>\n\n\n\n
    Rather than specify the items in a menu statically in the xml file, it’s possible to specify a function for Excel to call to get the menu items at run-time.<\/p>\n\n\n\n
    Instead of the “contextMenu” element, a “dynamicMenu” element is used instead:<\/p>\n\n\n
    \n<?xml version="1.0" encoding="UTF-8"?>\n<customUI xmlns="http:\/\/schemas.microsoft.com\/office\/2009\/07\/customui">\n    <ribbon>\n        <!-- The ribbon and context menus can be specified in the same file -->\n    <\/ribbon>\n    <contextMenus>\n        <contextMenu idMso="ContextMenuCell">\n            <dynamicMenu id="MyDynamicMenu"\n                label= "My Dynamic Menu"\n                imageMso="ChangeCase"\n                insertBeforeMso="Cut"\n                getContent="context_menus.dynamic_menu"\/>\n        <\/contextMenu>\n   <\/contextMenus>\n<\/customUI>\n<\/pre><\/div>\n\n\n
    The “getContent” tag specifies the Python function to call to get the contents of the menu. The function returns an xml fragment that will be used to construct the menu.<\/p>\n\n\n
    \ndef dynamic_menu(control):\n    """Return an xml fragment for the dynamic menu"""\n    xml = """\n         <menu xmlns="http:\/\/schemas.microsoft.com\/office\/2009\/07\/customui">\n            <button id="Menu2Button1" label="Upper Case"\n                imageMso="U"\n                onAction="context_menus.toupper"\/>\n \n            <button id="Menu2Button2" label="Lower Case"\n                imageMso="L"\n                onAction="context_menus.tolower"\/>\n \n            <button id="Menu2Button3" label="Proper Case"\n                imageMso="P"\n                onAction="context_menus.toproper"\/>\n         <\/menu>\n    """\n    return xml\n<\/pre><\/div>\n\n\n
    The code above should go in the “context_menus” module as in the previous section. Rather than using a simple string as above, any of the Python xml packages could be used to generate the menu programmatically.<\/p>\n\n\n\n
    For more details about the dynamicMenu<\/em> element see the Microsoft documentation 2.2.19 dynamicMenu (Dynamic Menu)<\/a>.<\/p>\n\n\n\n
    Other Menu Controls<\/h2>\n\n\n\n
    As well as buttons, used in the previous sections, other controls are available to use in context menus. The available control types are:<\/p>\n\n\n\n