Ribbon command button click events

[Joseph.Marro]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am stumped. Is it possible to capture the click event of a command button from the ribbon? I would like to piggy back off the click event to execute my code. The following post deals with the same question:<o></o>
http://www.mrexcel.com/forum/showthread.php?t=448695&highlight=Increase+Indent+click+event+WithEvent<o></o>
Unfortunately, I have been unable to make it work in 2007. Like the OP, I would like to capture the click events for the "Increase Indent" and "Decrease Indent" buttons. Any assistance would be greatly appreciated.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro

 

[Joseph.Marro]

Any takers? I am only looking for a starting point. I can't even locate the control when browsing through the object model.

Thank you,

Joseph Marro

 

[Andy Pope]

It is if you add a Command item to ribbonx.

In your code module use callbacks.

Public Sub rbxIndentDecrease_onAction(control As IRibbonControl, ByRef cancelDefault)
'
' Code for onAction callback. Ribbon control command
'
    MsgBox "Indent De"
    cancelDefault = False
End Sub
Public Sub rbxIndentIncrease_onAction(control As IRibbonControl, ByRef cancelDefault)
'
' Code for onAction callback. Ribbon control command
'
    MsgBox "Indent Increase"
    cancelDefault = False
End Sub

ribbonx would look like this. had to add extra space after lessthan sign to allow posting.

< customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
	< commands >
		< command 
			idMso="IndentDecreaseExcel"
			onAction="rbxIndentDecrease_onAction"/>
		< command 
			idMso="IndentIncreaseExcel"
			onAction="rbxIndentIncrease_onAction"/>
	< /commands >
< /customUI >

 

[Joseph.Marro]

Andy,

Thank you for the information and code. I will give it a try over the weekend and let you know how it goes. I miss 2003 command bars, the ribbon is so painful to work with.

Thank you,

Joseph Marro

 

[ZVI]

Joseph,
You can add via code the buttons with "OnAction" property the same way as in Excel 2003.
In Excel 2007+ the old-style buttons goes to the Add-Ins tab of the Ribbon

Sub Auto_Open()
  Dim cmd As CommandBarButton
  
  DeleteControls
  Set cmd = Application.CommandBars("Standard").Controls.Add(Type:=msoControlButton, Temporary:=True)
  With cmd
    .Caption = "Indent+"
    .TooltipText = "Click me to increase indent"
    .Style = msoButtonCaption
    .OnAction = "MacroIndentIncrease"
  End With
  
  Set cmd = Application.CommandBars("Standard").Controls.Add(Type:=msoControlButton, Temporary:=True)
  With cmd
    .Caption = "Indent-"
    .TooltipText = "Click me to decrease indent"
    .Style = msoButtonCaption
    .OnAction = "MacroIndentDecrease"
  End With
  
End Sub

Sub MacroIndentIncrease()
  MsgBox "It's MacroIndentIncrease"
End Sub
  
Sub MacroIndentDecrease()
  MsgBox "It's MacroIndentIncrease"
End Sub
  
Private Sub Auto_Close()
  DeleteControls
End Sub

Private Sub DeleteControls()
  On Error Resume Next
  With Application.CommandBars("Standard")
    .Controls("Indent+").Delete
    .Controls("Indent-").Delete
  End With
End Sub

It's possible due to the backward compatibility.

Regards,

 

[Joseph.Marro]

ZVI,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you for your post. After taking another look at my approach to implementing this solution I have decided to use your method. Having buttons on the Add-ins tab will be less likely to raise any confusion. Piggy backing off the existing buttons really doesn't let the user know that something special is happening when the button is pushed. I greatly appreciate you assistance.<o></o>
<o></o>
Andy,<o></o>
<o></o>
Thank you for the solution to my original question. I will defiantly use the approach in other projects. I greatly appreciate you assistance.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>