Excel Ribbon Toggle Button not running onAction or getPressed callbacks

Question

I have added a Toggle Button to a Ribbon via the Excel.CustomUI file, which is fine, but it won't actually trigger any callback functions. I've tried various combinations as given on the web, but cannot get any of the functions to run at all.

I also tried with a checkbox, but this did not run functions either. I checked and removed any security, but to no avail.

I have standard buttons which are working as required.

Am missing something?

This is the excel.customUI file

    <mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui' 
                       onLoad='ToggleAutoCalc' >
  <mso:ribbon>
    <mso:qat/>
    <mso:tabs>
      <mso:tab id='reportTab' label='ICit' insertBeforeQ='mso:TabFormat'>
        <mso:group id='reportGroup' label='DBRW Copy Paste' autoScale='true'>
          <mso:button id='DBRW Copy' label='DBRW Copy' 
imageMso='Copy'      onAction='DBRWCopyPaste.xlam!TM1Copy'/>
          <mso:button id='DBRW Paste' label='DBRW Paste' 
imageMso='Paste'      onAction='DBRWCopyPaste.xlam!TM1Paste'/>
          <mso:button id='DBRW UnDo' label='DBRW UnDo' 
imageMso='CellsDelete'      onAction='DBRWCopyPaste.xlam!TM1UndoPaste'/>
          **<mso:toggleButton id='AutoCalc' label='AutoCalc' 
           screentip='Toggle Autocalc'
           size='Large'
imageMso='ColumnSettingsMenu'      onAction='DBRWCopyPaste.xlam!TbtnToggleAutoCalc'
           getPressed='DBRWCopyPaste.xlam!GetPressed'/>**
        </mso:group>
      </mso:tab>
    </mso:tabs>
  </mso:ribbon>
</mso:customUI>

and this is my test VBA code:

Option Explicit

Public MyRibbon As IRibbonUI


Sub ToggleAutoCalc(ribbon As IRibbonUI)

Set MyRibbon = ribbon

Debug.Print "ToggleAutoCalc"

End Sub

Sub TbtnToggleAutoCalc(control As IRibbonControl, pressed As Boolean)
pressed = True
End Sub

Sub GetPressed(control As IRibbonControl, ByRef returnedVal)
Dim bAutoCalc As Integer
Dim sVal As String
Dim nVal As Integer

GetPressed = returnedVal


End Sub

aduguid · Accepted Answer · 2018-01-12T11:00:09.257

0

The single quotes should be double quotes in your XML. Also the id tag does not allow a name to have a space.

Are you using the Custom UI Editor to edit your XML? There is a feature in the tool to check if your XML is well formed. Here's a link https://www.rondebruin.nl/win/s2/win001.htm

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ToggleAutoCalc">
    <ribbon>
        <tabs>
            <tab 
                    id="reportTab"
                    label="ICit"
                    insertBeforeQ="TabFormat"
                    >
                <group 
                        id="reportGroup" 
                        label="DBRW Copy Paste" 
                        autoScale="true"
                        >
                    <button 
                            id="DBRW_Copy"
                            label="DBRW Copy" 
                            imageMso="Copy"      
                            onAction="TM1Copy"
                            />
                    <button 
                            id="DBRW_Paste" 
                            label="DBRW Paste" 
                            imageMso="Paste"      
                            onAction="TM1Paste"
                            />
                    <button 
                            id="DBRW_UnDo" 
                            label="DBRW UnDo" 
                            imageMso="CellsDelete"      
                            onAction="TM1UndoPaste"
                            />
                    <toggleButton 
                            id="AutoCalc" 
                            label="AutoCalc" 
                            screentip="Toggle Autocalc"
                            size="large"
                            imageMso="ColumnSettingsMenu"      
                            onAction="OnAction"
                            getPressed="GetPressed"
                            />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

In your VBA, first create a global variable like "IsPressed".

Then the "GetPressed" procedure should be using the "returnedVal" variable to return the value.

    Option Explicit

    Public MyRibbon As IRibbonUI
    Public IsPressed As Boolean

    Sub ToggleAutoCalc(ribbon As IRibbonUI)

        Set MyRibbon = ribbon

    End Sub

    Sub GetPressed(control As IRibbonControl, ByRef returnedVal)

        Select Case control.ID
          Case "AutoCalc"
            returnedVal = IsPressed
        End Select

    End Sub

    Sub OnAction(control As IRibbonControl, pressed As Boolean)

        Select Case control.ID
          Case "AutoCalc"
            IsPressed = pressed
            If IsPressed Then
              MsgBox IsPressed
            Else
              MsgBox IsPressed
            End If

        End Select

    End Sub

edited Jan 12 '18 at 11:00

answered Jan 12 '18 at 02:50

aduguid

3,099
6
18
37

Great - thanks for looking at this, Tony. I have updated the XML and VBA with the code, but, unfortunately the button still does not do anything when pressed (I put a breakpoint on the 'onAction' and 'GetPressed' VBA functions, but nothing happened). – NatHunter Jan 12 '18 at 09:34
No worries, mate. Can you check the question as answered? :) – aduguid Jan 12 '18 at 09:36
Great - thanks for looking at this, Tony. I have updated the XML and VBA with the code, but, unfortunately the button still does not do anything when pressed (I put a breakpoint on the 'onAction' and 'GetPressed' VBA functions, but nothing happened). I modified code from a previous post to build the XML and, iterestingly, the single quote string characters do work for a standard button. Does this mini project work for you? – NatHunter Jan 12 '18 at 09:41
Sorry, i was just on my phone and didn't scroll far enough down. Does the message box open when you click the toggle button? – aduguid Jan 12 '18 at 09:45
I ran the code in Excel 2016 and the toggle button opened the message box. – aduguid Jan 12 '18 at 09:52
Nothing happens. I tried the workbook on another machine and that didn't work either - again, nothing happened – NatHunter Jan 12 '18 at 10:31
I'll save my work in GitHub and post a link to the file. What version of Excel are you using? – aduguid Jan 12 '18 at 10:33
I've posted the example file here https://github.com/aduguid/Stack-Overflow-example – aduguid Jan 12 '18 at 11:01
You've put me onto the right track - the CustomUI Editor is the way to go, it seems! I have been generating the Excel.CustomUI file in code and putting it into the default location, but the Excel.CustomUI Editor seems to embed it into the xlam or xlsm file itself. Validating the code through the editor also threw up a few errors in the CustomUI section. Thanks for your assistance with this, Tony! – NatHunter Jan 12 '18 at 11:37
One question - is it possible to edit the CustomUI code in a text file? – NatHunter Jan 12 '18 at 11:38
You can, but its just a bit easier to use the tool XML tool. Microsoft evens shares out the tool solution so you build/edit it yourself. https://github.com/OfficeDev/office-custom-ui-editor – aduguid Jan 12 '18 at 11:47
I've have a few projects that you can use as examples here https://github.com/Office-projects – aduguid Jan 12 '18 at 12:34

Excel Ribbon Toggle Button not running onAction or getPressed callbacks

1 Answers1