Excel 2007 Ribbon ToggleButton

[Boswell]

I have an excel add-in that has a custom tab on the ribbon, and on this tab is a toggle button. The only action that occurs when the toggle button is pressed is that a cell is set equal to false or true.

Sub Toggle_is_clicked(control As IRibbonControl, pressed As Boolean)

    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets(1)
    If pressed = True Then
        ws.Cells(1, 11) = True
    Else
        ws.Cells(1, 11) = False
    End If
  
End Sub

The state of the cell linked to the toggle button affects what procedures occur when a macro is run.

My problem is that certain procedures should not be run while the toggle button is pressed. If the user forgets to unpress the toggle button when performing one of these procedures I would like to automatically unpress the button. It is easy to set the affected cell = false, but I would like the state of the cell and the toggle button to match. Does anyone know how to access and press a toggle button on the excel 2007 ribbon from a macro?

Thank you in advance.

 

[RoryA]

You need to use the getPressed callback and return True or False accordingly.

 

[Boswell]

Thank you for your reply rorya, I googled call backs... and came up with the following code:

XML toggle button declaration:

 toggleButton id="Tbtn" label="Toggle Button" 
                                 size="large"
                                 onAction="Toggle_is_clicked" 
                                 getPressed="GetPressed" 
                                 imageMso="Chart3DPieChart"/

Then I have the following code in VBA for GetPressed:

Public Function GetPressed(ByVal control As IRibbonControl) As Boolean
    GetPressed = False
End Function

My hope was to call the "GetPressed" routine whenever a macro is run that requires the toggle button be unpressed (in tandem with setting the affected cell equal to false)... but I am unable to call "GetPressed". The function call requires an argument, however using the toggle button ID, "Tbtn", returns type mismatch.

I may be completely misunderstanding callbacks. Any suggestions?

Thank you.

 

[RoryA]

Your callback declaration is wrong - it should be:

Sub GetPressed(control As IRibbonControl, _
                       ByRef pressed)
                       
    ' Callback getPressed

    pressed = ActiveWorkbook.Worksheets(1).Cells(1, 11).Value

End Sub

so that it reflects the cell value. (You need to invalidate the control after altering the cell value)

 

[Boswell]

Thanks rorya. I have altered my declaration to match yours, however I am still unable to call the function "GetPressed", for example at the beginning of a routine I have the following:

    ws.Cells(1, 11) = False
    Call GetPressed

This call returns "argument not optional". I have tried various combinations of arguments (control, true), (Tbtn, false), ("Tbtn", true) etc.

Sometimes I get no compiler error, but the toggle button remains pressed.

any suggestions on how i should call the fxn?

 

[RoryA]

You don't call it - the Ribbon does. You need to invalidate the control on the ribbon; can I assume that you have stored a reference to the Ribbon somewhere? If not, I suggest you read this.

 

[Boswell]

rorya, thanks for the link. I have read over it, and as far as I can tell am doing everything in line with what they are saying.

Is there a command for invalidating a control?... or (when i have everything done correctly) will the ribbon automatically recognize that the affected cell has been altered, and adjust the state of the toggle button accordingly?

I have tried commands such as "ribbon.Invalidate()", but to no avail.


Thanks in advance for any suggestions.

 

[RoryA]

Are you storing a reference to the Ribbon in a Public variable in your ****** callback? Assuming you are, you use that variable to invalidate the ribbon.

 

[Boswell]

This is probably giving me a lot more trouble than it should... below is what I am having no luck at now; I found these directions at the following link: http://msdn.microsoft.com/en-us/library/aa433869(office.12).aspx
which I found through the link you posted above.

In the XML markup file:
customUI … ******=”MyAddInInitialize” …

In the VBA code:
Dim MyRibbon As IRibbonUI

Sub MyAddInInitialize(Ribbon As IRibbonUI) (You declare this function in your VBA Markup)
    Set MyRibbon = Ribbon
End Sub

Sub myFunction()
     MyRibbon.Invalidate()         ‘ Invalidates the caches of all of this add-in’s controls    
End Sub

I can not validate my mark up file with the call back "******"... Is this what you are referring to when you say "storing a reference to the Ribbon"?

Thanks

(****** = On Load)

 

[Boswell]

rorya, thanks for all your help. I believe I have it working now. I could not validate my mark up file because i was capitalizing the O in the on load call back.