Excel Ribbon Toggle Button not running onAction or getPressed callbacks

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

Solution

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