Solved: Assign macros to ribbon-commands

Hi!
I am new here, and have poor knowledge about VBA, but I still want to try. I will try to explain my problem.

I have managed to add a new TAB called ERGO to my ribbon in Word 2007. On this TAB, I have managed to put GROUPS and COMMAND-BUTTONS. But what I have not managed, is to assign macros (or other commands) to the COMMAND-BUTTONS.

I don?t know whether this should be done by macros or with programming, or in another way? In Word 2003 it is a simple task, but I can?t figure out how it should be done in Word 2007? Can anyone please help me?

I assume you're using the "MS Office 2007 Custom UI Editor" from

http://openxmldeveloper.org/articles...mUIeditor.aspx


The right most "tab" generates the Callback functions for your controls, etc. Sample of one of mine below


[vba]
'Callback for customUI.onLoad
Sub OnRibbonLoad(ribbon as IRibbonUI)
End Sub
'Callback for bCross onAction
Sub ShadePressed(control as IRibbonControl)
End Sub
'Callback for togDetailed onAction
Sub ViewPressed(control as IRibbonControl, pressed as Boolean)
End Sub
'Callback for togDetailed getPressed
Sub ViewPressedGet(control as IRibbonControl, ByRef returnedVal)
End Sub
[/vba]


You need to copy and paste these (your's not mine:rotlaugh:) into a module and then add your macro calls, etc.

[vba]
Sub SortPressed(control As IRibbonControl)
Select Case control.id

Case "bSort1"
Call SortOpt(1)
Case "bSort2"
Call SortOpt(2)
End Select
End Sub
[/vba]

Additional night time reading -- Ken's book has a sample chapter available

http://www.excelguru.ca/node/93

as does Stephen Bullen

http://www.oaltd.co.uk/Spreads/Excel...ogRef_ch14.pdf


Both very insightful, and worth ordering (don't seem to be on the shelf in my local book store)


MS also has Office Fluent User Interface Developer Portal

http://msdn.microsoft.com/en-us/office/aa905530.aspx

Paul

Hi Paul

Thank you for nice links and tips about how to write callbacks. Unfortunately my knowledge about VBA is poor, so I should have more specific instructions to get this right.

I’ll try to explain exactly what I have done, so there is no doubt about that.

1. As you assume, I use the MS Office 2007 Custom UI Editor.
2. I am working with Word (I suppose the same things could be done in Excel).
3. With help from great copying, I’ve made a file containing the following VBA-code, who definates a button id, a label and an on action name.


This works perfectly (except from a warning about macro-function when opening Word), and it’s rather good looking, but unfortunately not working.

4. This file is stored in the startup folder for Word.

To understand this better, it would be great help for me to know:

5. Should further code be put into the VBA-code in the file mentioned above, or should it be put into a separate file?
6. If separate file, should this file be designed in the MS Office 2007 Custom UI Editor?
7. If separate file, should this file also be put in the startup-folder, or should it be put somewhere else?
8. If separate file, could I have callbacks for all commands in the same file, or should I have separate files for each command?
9. For all commands, I want to open a Template. I have made templates of a number of documents which are used pretty often. When open these templates (at least by exploring and double-click actual files) I have managed a fill-form to open, and then, with help from bookmarks, the information I state, will be filled into my document at the right places. For example one of these templates could be stored as c:/macro/engagementletter.docx.

Can someone please help me with how this a callback should be designed (sorry, but I probably need it word by word)?


Thanks again for spending your time helping me. I’ll get these books to learn more about VBA, but in the short run, I would be pleased just to make this work.


Best regards
Jan Aage

Jan

I was only talking about the way that a macro is "connected" via Callbacks to the Ribbon.

Each type of Ribbon control has it's own callback format that gets called (over simplifaction) when you select, click, or otherwise do something on the ribbon.

So when you have XML for a button control .....

[vba]
<button id="bWhite"
label="White"
showLabel="false"
onAction="ShadePressed"
image="White"
screentip="Clear background and borders from selected cells"
/>
[/vba]

.... clicking that control calls the onAction="ShadePressed" callback, passing the IRibbonControl object bWhite ....

[vba]
Sub ShadePressed(control As IRibbonControl)
.....Your VBA here .....
End Sub
[/vba]

You still need the VBA to actually do things, but you can test the .ID attribute if you need to. I usually have the same callback Sub for multiple controls if there's a lot of common logic

Help??

Paul

Jan

I was only talking about the way that a macro is "connected" via Callbacks to the Ribbon.

Each type of Ribbon control has it's own callback format that gets called (over simplifaction) when you select, click, or otherwise do something on the ribbon.

So when you have XML for a button control .....

[vba]
<button id="bWhite"
label="White"
showLabel="false"
onAction="ShadePressed"
image="White"
screentip="Clear background and borders from selected cells"
/>
[/vba]

.... clicking that control calls the onAction="ShadePressed" callback, passing the control bWhite ....

[vba]
Sub ShadePressed(control As IRibbonControl)
.....Your VBA here .....
End Sub
[/vba]

You still need the VBA to actually do things, but you can test the .ID attribute if you need to. I usually have the same callback Sub for multiple controls if there's a lot of common logic


Paul

Hi again!

In the file containing the VBA code to change my Ribbon, this code is a part of my VBA:

<button id="Btn7"label="Engasjementsbrev"onAction="MyActionMacro7"/>

I now have added Module1 to this document, an in this module I have added the following sub:

Sub MyActionMacro7()

ChangeFileOpenDirectory "C:\Makro-dokumenter\"
Documents.Open FileName:="""x9 - Engasjementsbrev.dotm""", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto, XMLTransform:=""

End Sub

I would hope this could make my macro work, but it doesn't. When click Btn7, this message pops up:

"Wrong number of arguments or invalid property assignment."

Any suggestions anyone??

Thanks!

The Callback from the onAction has a very specific format which varies depending on the type of control

The CustomUI editor has a Call back generator button that will generate the correct call back skelton depending on the controls and the onAction attributes in your XML


[vba]
<button id="Btn7"label="Engasjementsbrev"onAction="Btn7Pressed"/>

[/vba]



Notice the format of the button callback


[vba]

Sub Btn7Pressed(control As IRibbonControl)

'Msgbox control.ID
Call MyActionMacro7
End Sub

[/vba]

Paul