I have an issue in being able to store a reference to my custom Ribbon. I can therefore only invalidate a dropDown menu in my Ribbon once, then my reference turns to "Nothing". Two modules are used for one Ribbon, is that a major no-no?
I have done my best to follow the very useful guide of https://gregmaxey.com/word_tip_pages/customize_ribbon_main.html
To organize my scripts, I have created two modules: "Logic" and "RibbonControl". "Logic" is generally meant to handle the main logic of the Ribbon. "RibbonControl" is generally meant to handle interaction with the Ribbon (getting number of items in a drop-down etc.). Note: this is a simplification and some things are renamed.
The problem I have is with a dropDown-menu. It works once, but can never be invalidated. The ribbon-xml starts like this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonControl.OnLoad">
<ribbon>
<tabs>
<tab id="Coloring" label="Coloring" insertBeforeQ="HelpTab">
One of the groups in it has the erroring dropDown label:
<group id="ColorSelGrp" label="Coloring Selection" >
<dropDown id="SelChoose" label="Choose coloring"
getItemCount="RibbonControl.GetSelectionItemCount"
getItemLabel="RibbonControl.GetSelectionItemLabel"
getSelectedItemIndex="RibbonControl.GetSelectionItemIndex"
onAction="RibbonControl.SelectWhatToMark" />
<button id="RefreshColSel" label="Refresh" image="refresh" onAction="RibbonControl.RefreshSelectionList" size="large" />
<button id="ColorSel" label="Color" image="sel_color_1" onAction="Logic.ColorSelection" size="large" />
</group>
At the start of my module "RibbonControl" I declare a public IRIbbonUI among other globals:
Option Explicit
Public myRibbon As IRibbonUI
Public selectionList(50) As String
Public selectionListCount As Integer
Public selectionActiveId As Integer
I then have a load function:
OnLoad(ribbon As IRibbonUI)
Set myRibbon = ribbon
Logic.OnLoad
End Sub
The load function links to the other module's OnLoad-function to allow both modules to start up when the Ribbon is loaded up. I did like this because I didn't know how or if you even can have multiple onLoad functions referenced in the XML. I have also tested to send the ribbon reference to the Logic module through this Logic.OnLoad.
The other relevant RibbonControl functions are:
Sub GetSelectionItemCount(ByVal control As IRibbonControl, ByRef count)
count = selectionListCount
End Sub
Sub GetSelectionItemLabel(ByVal control As IRibbonControl, index As Integer, ByRef label)
label = selectionList(index)
End Sub
Sub GetSelectionItemIndex(ByVal control As IRibbonControl, selectedID As String, selectedIndex As Integer)
Select Case control.ID
Case Is = "Choose"
selectedIndex = 0
Case Else
'Do nothing
End Select
End Sub
Sub SelectWhatToMark(ByVal control As IRibbonControl, selectedID As String, selectedIndex As Integer)
selectionActiveId = selectedIndex
End Sub
The issue is in this function below. It starts with searching for keywords to build a list of objects to select from a list. That works, so I have redacted it from here.:
Sub RefreshSelectionList(ByVal control As IRibbonControl)
'Here is a regex to find keywords in the document and save to array.
'It works, so it's redacted to simplify and not overshare.
If Not myRibbon Is Nothing Then
myRibbon.InvalidateControl "PatternChoose"
End If
End Sub
So yes, this code works, once. In a document with the intended text, when I press the "Refresh" button, the dropDown-list goes from being empty to getting filled with all the intended things you should be able to select from.
I can select them and then press the separate button with code in the "Logic" module and it will find the correct instances and do what is expected. But if I remove part of the text and press Refresh, the expected result is that the DropDown should be invalidated and reloaded with a now smaller list of things to select from.
The issue is hinted at the fact that I "hide" the invalidation within a "Nothing"-check. When I try to use the Refresh-button again, the myRibbon-reference is indeed "Nothing". So it cannot find the active ribbon and invalidate the dropDown, so the list looks the same. The logic works however, so if I first refresh with a full list, remove some options and refresh, the dropDown looks the same, but the values are not linked correctly. Ex: First refresh: "A, B, C, D, E, F" I remove all A and B and refresh: "A, B, C, D, E, F". But if I select "A", it will act as if "C" is selected. So the main issue seems to be the missing invalidation.
So what am I doing wrong to make it not save the reference? Is it not possible to use two modules? Or is there something else, possibly blatantly obvious that I have missed?
A "minor" issue (but still needs to be solved) is that When I press the "Refresh"-button I get the pop-up "Argument not optional". I haven't figured out what argument I have forgotten.
Apologies if this is a duplicate. I have searched to the best of my ability and could not find any question anywhere where some had this particular issue.
Thank you for your comment and link, Timothy Rylatt. I couldn't get it to work with the link that you sent, but through it and some other searching I got to a solution that seems to work.
This is the new onLoad. It saves a variable connected to the document. The delete is necessary for opening multiple times (based this on this page: MS guide for document variables)
Sub OnLoad(ribbon As IRibbonUI)
Set myRibbon = ribbon
Logic.OnLoad
Dim lngRibPtr As Long
lngRibPtr = ObjPtr(ribbon)
ThisDocument.Variables("RibbonRef").Delete
ThisDocument.Variables.Add Name:="RibbonRef", Value:=lngRibPtr
MsgBox ThisDocument.Variables("RibbonRef").Value
End Sub
This is the refresh-function, I just call it when I want to invalidate anywhere.
Public Sub RefreshRibbon()
If myRibbon Is Nothing Then
If Not ThisDocument.Variables("RibbonRef") Is Nothing Then
Set myRibbon = GetRibbon(ThisDocument.Variables("RibbonRef"))
myRibbon.Invalidate
End If
Else
myRibbon.Invalidate
End If
End Sub
It calls on a special GetRibbon function that transforms the Long back to a IRibbonUI reference.
Public Function GetRibbon(ByVal lRibbonPointer As Long) As Object
Dim objRibbon As Object
CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
Set GetRibbon = objRibbon
Set objRibbon = Nothing
End Function
I based my solution mostly on this article: Lost state handling, Excel
It is however made for Excel so I based my modifications on the comments in that article. It did not work since the "CopyMemory" that was used is not something inbuilt in VBA (at least my VBA was completely ignorant of it). So therefore I had to add a declaration of it at the top of my module. I found that here: CopyMemory-Function
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
(lpDest As Any, lpSource As Any, ByVal cbCopy As Long)
Further sources that helped me along that could be of interest for someone still struggling:
https://learn.microsoft.com/en-us/office/vba/api/office.documentproperties.add
What happens to the Word session ribbon after closing and reopening a document with a custom ribbon?
There is still an issue however in that every time I refresh, the "invalidate" line(s) generate the message "Argument not optional" (but since the ribbon gets invalidated and correctly updated, it clearly is optional... whatever it is). All examples that I have seen so far just use it on its own like I do here, so I have not been able to figure out what argument I am supposed to use.