Ribbon Customizatin AddIn

[Greg Maxey]

This is a long post with multiple questions. Please don't be discouraged
and feel free to answer all or part. Thank you.

The other day I became interested in creating an Template AddIn that will
add a group to the built-in Add-Ins tab. The group holds one Dropdown. I
wanted the dropdown item count, labels, and OnAction events to be dynamic
based on information stored in a table contained in the AddIn.

The RibbonX was fairly simple. I just added a new group to the Add-Ins tab
that contained one dropdown control:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
onLoad="Onload">
<ribbon>
<tabs>
<tab idMso="TabAddIns">
<group id="Grp1" label="Proof Reading">
<dropDown id="DD1" label="Add Marks" getItemCount="GetItemCount"
getItemLabel="GetItemLabel"
getSelectedItemIndex="GetSelectedItemIndex"
onAction="MyDDMacro">
</dropDown>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

I used call backs to set the dropdown list count, the label for each list
item, and the default displayed list item

As a practical exercise (and prompted by a another post in the newsgroups) I
decided to use the OnAction callback to insert pre-definded comments in the
text at the selection. I used a two column table in the Addin to define the
dropdown labels and default comment text. E.g.,

Column 1 Column2

Select error
agree. Agreement of subject and verb
ant. Antecendnet. "the person believed that
they." The sentence is incorrect because person is singular, and they is
plural

The code in my VB Project (with comments) is as follows:

Option Explicit
Public myRibbon As IRibbonUI
Private myArrayPri() As String
Private myArraySec() As String

'This procedure fires when the Add-In is loaded. Its purposes is to load
the values contained in the template Add-In table into a pair of arrays.
myArrayPri() holds the data to be used for the dropdown labels.
myArraySec() holds the data for the predefined comment text. The procedure
opens a new document based on the template AddIn and populates the arrays
with data in the tables. The new document is then closed without saving
changes.

Sub AutoExec()
Dim aTemplate As Template
Dim oDoc As Word.Document
Dim oTbl As Table
For Each aTemplate In Templates
If aTemplate.Name = "Proofreading Marks.dotm" Then
Set oDoc = Documents.Add(aTemplate.FullName, , , False)
Set oTbl = oDoc.Tables(1)
myArrayPri() = GetErrorArray(oTbl)
myArraySec() = GetDescriptiveArray(oTbl)
oDoc.Close wdDoNotSaveChanges
Exit For
End If
Next
End Sub

'This procedure fires when the template AddIn is opened directly for editing
(i.e., either the VB code or the table data)

Sub AutoOpen()
AutoExec
End Sub

'The next two function actually populate the two arrays.

Function GetErrorArray(ByVal oTbl As Table) As String()
Dim i As Long
Dim tempArray() As String
ReDim tempArray(oTbl.Rows.count)
For i = 1 To oTbl.Rows.count
tempArray(i - 1) = Left(oTbl.Cell(i, 1).Range.Text, Len(oTbl.Cell(i,
1).Range.Text) - 2)
Next i
GetErrorArray = tempArray
End Function


Function GetDescriptiveArray(ByVal oTbl As Table) As String()
Dim i As Long
Dim tempArray() As String
ReDim tempArray(oTbl.Rows.count)
For i = 1 To oTbl.Rows.count
tempArray(i - 1) = Left(oTbl.Cell(i, 2).Range.Text, Len(oTbl.Cell(i,
2).Range.Text) - 2)
Next i
GetDescriptiveArray = tempArray
End Function

'This is a RibbonX callback needed to create a ribbon object (I think that
is what it does???)

Sub Onload(ribbon As IRibbonUI)
Set myRibbon = ribbon
End Sub

'This RibbonX callback establishes the number of items in the dropdown. The
"id" of the dropdown created by the RibbonX is "DD1." The count of items is
= to the number of items in myArrayPri()

Sub GetItemCount(ByVal control As IRibbonControl, ByRef count)
Select Case control.id
Case "DD1"
count = UBound(myArrayPri)
Case Else
'Do Nothing
End Select
End Sub

'This RibbonX callback is used to define the labels for each item in the
dropdown. The procedure is called once for each item in the dropdown:

Sub GetItemLabel(ByVal control As IRibbonControl, Index As Integer, ByRef
label)
Select Case control.id
Case "DD1"
label = myArrayPri(Index)
Case Else
'Do nothing
End Select
End Sub

'This RibbonX callback sets the default dropdown item to display when the
Ribbon control is created. In this AddIn, I want "Select error" to be
displayed intially and after each OnAction event:

Sub GetSelectedItemIndex(ByVal control As IRibbonControl, ByRef Index)
Select Case control.id
Case "DD1"
Index = 0
Case Else
'Do nothing
End Select
End Sub

'This RibbonX callback is fired when the user selects one of the items in
the dropdown. It does several things:

'1. Determines if there is an open document. If not, the control is
invalidated (causes the control to be rebuilt with "Select error" displayed)
'2. Determines if there is text selected to comment on. If not the control
is invalidated
'3. Uses the dropdown item selected to insert a custom (customizable)
comment at the selection
' a. Displays a userform containing pre-defined comment text
' b. Temporarily sets the Application.UserInitials = the contol label
text

Sub MyDDMacro(ByVal control As IRibbonControl, selectedId As String,
selectedIndex As Integer)
Dim oFrm As UserForm1
Dim pUserInt As String
Select Case control.id
Case "DD1"
If Documents.count < 1 Then
myRibbon.InvalidateControl control.id
Exit Sub
End If
If Selection.Type = wdSelectionIP Or wdNoSelection Then
MsgBox "Please select the proofreading error in the text before
inserting comments."
myRibbon.InvalidateControl control.id
Exit Sub
End If
Select Case selectedIndex
Case Is = 0
'Do Nothing
Case Else
pUserInt = Application.UserInitials
Set oFrm = New UserForm1
oFrm.TextBox1 = myArraySec(selectedIndex)
oFrm.Show
Application.UserInitials = myArrayPri(selectedIndex)
Selection.Comments.Add Selection.Range, oFrm.TextBox1.Text
Unload oFrm
Set oFrm = Nothing
Application.UserInitials = pUserInt
myRibbon.InvalidateControl control.id
End Select
Case Else
'Do Nothing
End Select
End Sub


The UserForm contains one textbox and one command button. The textbox is
set for multi-line. The command button code is:

Private Sub CommandButton1_Click()
Me.Hide
End Sub

Problems/Questions:

1. I am not very confident of my process for populating the two arrays with
the data needed for the callbacks. My goals is to find the simplest way to
make the data customizable to the user and accessible to the the callback.
By putting the data in a table contained in the template. All the user needs
to do is open the template and edit the table. Once that is done, then when
the template loads the the compiler has to get to it. AutoExec is the only
event that I know of that fires when a template is loaded. Is this correct
and is this the best way?

2. a. I have not been able to isolate why, but when I put my Addin in the
Word Startup Folder and start Word I always get an error message "Subscript
out of range."
b. If I remove the AddIn from the Startup folder and attempt to load
the Add-In manually, I get a get the same message: "Subscript out of
range."
c. If I leave Word running, remove the AddIn, and load it a second time
it loads and works perfectly without error.
d. The error message never happens when I open the AddIn directly. I
assume that this problem is caused by some sort of timing conflict with the
AutoExec procedure and maybe I could correct it with a Do Events
statement but I am not very familiar with those and I don't know how to
proceed.

Any thoughts or ideas to fix this?

I'll be happy to share the complete template with anyone that is interested
in assisting to resolve the issue above. Just contact me vai my website
feedback at the link below and I will send it as an attachment.


Thanks.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Greg Maxey]

As is often the case, if I write out and post my problems I am able to see
things from a different slant and I can find a workable solution.

I am still not sure why the error didn't reoccur when I removed and reloaded
the AddIn, but the subcript out of range error was occuring in the
GetItemCount procedure:

I modified it to add an error handler and changed the AutoExec macro to a
procedure named "LoadArrays"

Sub GetItemCount(ByVal control As IRibbonControl, ByRef count)
On Error GoTo Err_Handler
Select Case control.id
Case "DD1"
count = UBound(myArrayPri)
Case Else
'Do Nothing
End Select
Exit Sub
Err_Handler:
LoadArrays
Resume
End Sub

In changed the AutoOpen procedure to:

Sub AutoOpen()
LoadArrays
End Sub

Apparently the RibbonX tries to build the Ribbon using the callbacks before
the AutoExec event fires but not before the AutoOpen event fires.

I am assuming that since I didn't get the error when I simply opened the
template AddIn directly. In that scnenario the AutoOpen event fired, called
the AutoExec which loaded the arrays and then the Ribbon built the controls
without error. Without the AutoOpen event the RibbonX tries to build the
controls before the AutoExec event fires. I proved this by adding a msgbox
in the AutoExec event. I put the Addin in Word Startup directory, started
Word, the error was generated, I continued, and then the msgbox in the
AutoExec event displayed.

Does anyone have suggestion for a better way? I hate to depend on an error
handler and and an error that I know is going to happen to make this all
work.

I thought about and perhaps foolishly tried:

If IsNull(myArrayPri(0)) Then
LoadArrays
End If

but, again since the array hasn't been created yet that results in the same
error.

Thanks.






--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Jean-Guy Marcil]

This is a long post with multiple questions. Please don't be discouraged
and feel free to answer all or part. Thank you.

I have not really worked with 2007 VBA or XML yet... In fact, I have looked
into 2007 because I was curious, but my working environment has not yet
allowed me to really take the plunge yet...
As I read your post, something did occur to me, and I tought that it might
be of interest to you, so here we go!

1. I am not very confident of my process for populating the two arrays with
the data needed for the callbacks. My goals is to find the simplest way to
make the data customizable to the user and accessible to the the callback.
By putting the data in a table contained in the template. All the user needs
to do is open the template and edit the table. Once that is done, then when
the template loads the the compiler has to get to it. AutoExec is the only
event that I know of that fires when a template is loaded. Is this correct
and is this the best way?

I do not like the idea of telling users to open a template... Too many
things could go wrong...

If I were developping such a customizable tool. I would include a Ribbon
button called "Customize Entries" (or whatever is appropriate). The button
would get the information from the template, display it in a table in a new
unsaved document, then the user would edit that document. this document, when
active, would have something like a MACROBUTTON labelled "Done" or some such
label (I guess it could be an ActiveX button instead). The user would click
on that to save the changes back to the template. (Or the button could be on
the Ribbon instead, excepts that this is more difficult becasue if the user
decides to give up and not close the document properly or in an unexcepted
way, you may be stuck with a useless Ribbon button).
Finally, since you already have the code to populate the dropdowns on the
ribbon, I guess you would need to execute that code to update the ribbon as
soon as the user has finished updating the data.

Just my 2 cents!

 

[Greg Maxey]

JGM,

Thanks for your interest and your comments.

I share your dislike for allowing the end user direct access to the
template. Your suggestion gave me and idea but even your suggestion and my
idea ultimately allows the user to edit the template. I suppose that I
could have the table of data in a separate document but since this whole
exercise was to try to gain a little mastery over Ribbon customization vice
building a robust template Add-In, I think I am going to go with a variation
of your idea.

I decided to add a toggle button control to the Ribbon. The initial state
is "Edit Proofreader Marks List" and the toggled state is "Save changes."

BTW I hope that by the time you get around to playing with this stuff that
you don't experience the same hair pulling frustrations that I have.
Getting the correct images, labels, and states set on a Toggle Button was
gruelling exerience ;-)

Clicking the TB opens the template as document allowing the user to make
their desired changes. I put plenty of Warnings, Cautions, and Notes in the
template so hopefully no one should break it unless they want to. When the
user is finished with the changes then they click the "Save changes" toggle.
The arrays are are rebuilt, the Ribbon revalidates and the changes appear in
the dropdown. Seems to work.

I am going to tinker with this a few more days and then perhaps post it as a
working example of Ribbon Customization on my website. I am including the
current code for you and anyone else that is interested. I might have your
e-mail address lying around or if you want send me a message and I will
attach the template as a reply.

Thanks again.

Option Explicit
Public myRibbon As IRibbonUI
Private myArrayPri() As String
Private myArraySec() As String
Private myArrayTri() As String
Private oDocTemp As Word.Document
Private pImage As String
Private bLabelState As Boolean

Sub Onload(ribbon As IRibbonUI)
Set myRibbon = ribbon
End Sub

Sub GetImage(control As IRibbonControl, ByRef image)
Select Case control.id
Case "TB1"
If pImage = "" Then pImage = "FileOpen"
image = pImage
Case Else
'Do Nothing
End Select
End Sub

Sub GetPressed(control As IRibbonControl, ByRef returnValue)
Select Case control.id
Case "TB1"
If pImage = "" Then pImage = "FileOpen"
If pImage = "FileOpen" Then
returnValue = False
Else
returnValue = True
End If
Case Else
'Do nothing
End Select
End Sub

Sub ToggleOnActionMacro(ByVal control As IRibbonControl, bToggled As
Boolean)
If bToggled Then
pImage = "FileClose"
EditPRMarks
LoadArrays
bLabelState = False
Else
pImage = "FileOpen"
SavePRMarksChanges
bLabelState = True
End If
myRibbon.InvalidateControl control.id
myRibbon.Invalidate
End Sub

Sub MyDDMacro(ByVal control As IRibbonControl, selectedId As String,
selectedIndex As Integer)
Dim oFrm As UserForm1
Dim pUserInt As String
Select Case control.id
Case "DD1"
If Documents.count < 1 Then
myRibbon.InvalidateControl control.id
Exit Sub
End If
If Selection.Type = wdSelectionIP Or wdNoSelection Then
MsgBox "Please select the proofreading error in the text before
inserting comments."
myRibbon.InvalidateControl control.id
Exit Sub
End If
Select Case selectedIndex
Case Is = 0
'Do Nothing
Case Else
pUserInt = Application.UserInitials
Set oFrm = New UserForm1
oFrm.TextBox1 = myArraySec(selectedIndex)
With oFrm.TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
oFrm.Show
If oFrm.Tag = 1 Then
Application.UserInitials = myArrayPri(selectedIndex)
Selection.Comments.Add Selection.Range, oFrm.TextBox1.Text
End If
Unload oFrm
Set oFrm = Nothing
Application.UserInitials = pUserInt
myRibbon.InvalidateControl control.id
End Select
Case Else
'Do Nothing
End Select
End Sub

Sub GetLabel(ByVal control As IRibbonControl, ByRef label)
Select Case control.id
Case "TB1"
If bLabelState Then
label = "Edit Proofreading Marks List"
Else
label = "Save changes"
End If
Case Else
'Do Nothing
End Select
End Sub

Sub GetSelectedItemIndex(ByVal control As IRibbonControl, ByRef Index)
Select Case control.id
Case "DD1"
Index = 0
Case Else
'Do nothing
End Select
End Sub

Sub GetItemLabel(ByVal control As IRibbonControl, Index As Integer, ByRef
label)
Select Case control.id
Case "DD1"
label = myArrayPri(Index)
Case Else
'Do nothing
End Select
End Sub

Sub GetItemScreenTip(ByVal control As IRibbonControl, Index As Integer,
ByRef screentip)
Select Case control.id
Case "DD1"
screentip = myArrayTri(Index)
Case Else
'Do nothing
End Select
End Sub

Sub GetItemCount(ByVal control As IRibbonControl, ByRef count)
On Error Resume Next
If IsNull(myArrayPri(0)) Then
LoadArrays
End If
On Error GoTo 0
Select Case control.id
Case "DD1"
count = UBound(myArrayPri)
Case Else
'Do Nothing
End Select
End Sub

Function GetErrorArray(ByVal oTbl As Table) As String()
Dim i As Long
Dim tempArray() As String
ReDim tempArray(oTbl.Rows.count)
For i = 1 To oTbl.Rows.count
tempArray(i - 1) = Left(oTbl.Cell(i, 1).Range.Text, Len(oTbl.Cell(i,
1).Range.Text) - 2)
Next i
GetErrorArray = tempArray
End Function

Function GetDescriptiveArray(ByVal oTbl As Table) As String()
Dim i As Long
Dim tempArray() As String
ReDim tempArray(oTbl.Rows.count)
For i = 1 To oTbl.Rows.count
tempArray(i - 1) = Left(oTbl.Cell(i, 2).Range.Text, Len(oTbl.Cell(i,
2).Range.Text) - 2)
Next i
GetDescriptiveArray = tempArray
End Function

Function GetScreenTipArray(ByVal oTbl As Table) As String()
Dim i As Long
Dim tempArray() As String
ReDim tempArray(oTbl.Rows.count)
For i = 1 To oTbl.Rows.count
tempArray(i - 1) = Left(oTbl.Cell(i, 3).Range.Text, Len(oTbl.Cell(i,
3).Range.Text) - 2)
Next i
GetScreenTipArray = tempArray
End Function

Sub AutoOpen()
LoadArrays
End Sub

Sub LoadArrays()
Dim aTemplate As Template
Dim oDoc As Word.Document
Dim oTbl As Table
bLabelState = True
For Each aTemplate In Templates
If aTemplate.Name = "Proofreading Marks AddIn.dotm" Then
Set oDoc = Documents.Add(aTemplate.FullName, , , False)
Set oTbl = oDoc.Tables(1)
myArrayPri() = GetErrorArray(oTbl)
myArraySec() = GetDescriptiveArray(oTbl)
myArrayTri() = GetScreenTipArray(oTbl)
oDoc.Close wdDoNotSaveChanges
Exit For
End If
Next
End Sub

Sub EditPRMarks()
Dim aTemplate As Template
Dim oTbl As Table
For Each aTemplate In Templates
If aTemplate.Name = "Proofreading Marks AddIn.dotm" Then
Set oDocTemp = aTemplate.OpenAsDocument
End If
Exit For
Next
End Sub

Sub SavePRMarksChanges()
Dim oTbl As Table
If Not oDocTemp Is Nothing Then
With oDocTemp
Set oTbl = .Tables(1)
myArrayPri() = GetErrorArray(oTbl)
myArraySec() = GetDescriptiveArray(oTbl)
myArrayTri() = GetScreenTipArray(oTbl)
.Save
.Close
End With
End If
End Sub

The UserForm code:

Option Explicit
'OK Btn
Private Sub CommandButton1_Click()
Me.Tag = 1
Me.Hide
End Sub
'Cancel Btn
Private Sub CommandButton2_Click()
Me.Tag = 0
Me.Hide
End Sub





--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org
Word MVP web site http://word.mvps.org~~~~~~~~~~~~~~~~~~~~~~~~~~