Ribbon Caption

Add-in Express™ Support Service
That's what is more important than anything else

Only registered users are allowed to subscribe.
Ribbon Caption
Change of Ribbon Caption 
Michael Kaden




Posts: 105
Joined: 2017-11-15
Hello,

1.) We want the change the Ribbon Caption (load strings from the resource file) depending on project settings, when the workbook is opened. In VSTO I did that in

ThisAddIn Class with

Sub Application_DocumentChange() Handles Application.WorkbookActivate

Where the Ribbon XML has getLabel = "GetLabel"


Public Function GetLabel(controlID) As String
If ControlID.ID = B01 then GetLabel = "Hallo"
'.....More conditions follow
End Function


How do we load specific Ribbon.Caption on start of the workbook with ADX? VB example please


2.) Then we want to change Ribbon Captions during run when the user changes the project settings

In VSTO we did that with

Ribo.ribbon.Invalidate()
Ribo.ribbon.ActivateTab("T01")


How do we invalidate (renew) the Ribbon.Caption in RunTime with ADX? VB example please

regards

Michael
Posted 08 Dec, 2017 08:27:56 Top
Andrei Smolin


Add-in Express team


Posts: 14583
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
We want the change the Ribbon Caption


anADXRibbonButton.Caption = "new value" 'Invalidate() is called automatically

But there's a difference. In VSTO, you always deal with separate instances (say, in different Excel windows) of a given Ribbon control; in Add-in Express, you deal with a component (e.g. ADXRibbonButton) that deals with control instances itself.

The code line above sets a value that *all* instances will share. To show different values of a given property (e.g. Caption) in different windows, you need to handle the PropertyChanging event of the ADXRibbonButton component. In the code of the event handler, 1) you find the property type that Office requests, 2) find the context in which the control is about to be shown and calculate the desired value of that property, 3) return the calculated value. Find more details and code sample in section Updating Ribbon Controls at Run Time, see the PDF file in the folder {Add-in Express}\Docs on your development PC.

Also, you can use any free C# to VB.NET convertor available on the web if you find a code sample in C# with no VB.NET sample.

To activate a tab, you call ADXRibbonTab.Activate(); invalidates are performed by components.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 Dec, 2017 08:49:20 Top
Michael Kaden




Posts: 105
Joined: 2017-11-15
Dear Andrei, thank you for your quick response.

I must explain it better.

When we change the caption, we will change them for all instances.

1.) We have different resource files for the Ribbon of this AddIn (these are actual languages as a user, even if he has for example English culture, wants to run our AddIn in French.

RibbonCaptionFrench.resx
RibbonCaptionEnglish.resx

the user selects his desired language and this is stored in his registry

If the user starts a new Excel Instance it shall look up the registry language spec and use the appropriate resource file to populate the Ribbon Caption.


So I am looking for the place where to put the method to load the right resource before the Ribbon is loaded.

regards

Michael
Michael
Posted 08 Dec, 2017 09:30:49 Top
Michael Kaden




Posts: 105
Joined: 2017-11-15
Dear Andrei,

I have quite some head eggs trying to transfer our VSTO project to ADX. Its like learning yet another language. My history over 40 years = Fortran4 - Basic - VB4 - VB6 - VB.net - VSTO and now ADX. But I think ADX has some very professional features, and overcomes some short falls of VSTO like UDFs, and so I will just bite through this. Thanks for your patience and help, and sorry for sometimes asking the obvious.

to my second point - change caption during runtime. I can change it by utilising Ribbon click

Private Sub But002_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles But002.OnClick
On Error GoTo Errorhandler
But001.Caption = "Hallo Click"
Exit Sub
Errorhandler:
Call serv.ECall(Err.Number, Err.Description, Err.Source, "B001 Click")
Resume Next
End Sub

But I want to change it from outside AddinModule and I tried:


Public Sub ResChange(NewRes As String)
On Error GoTo Errorhandler
Dim serv as New Service
Dim AdRib As New AddinModule
AdRib.But001.Caption = NewRes
Exit Sub
Errorhandler:
Call serv.ECall(Err.Number, Err.Description, Err.Source, "ResChange")
Resume Next
End Sub

Nothing happens but also no Error

So how should this Sub look like? I tired to find a solution in the documentation, but as far as I understand this,

adxRibbonButton1.Caption = "New Caption" or
Private Sub AdxRibbonButton1_PropertyChanging(

does also not cover my point. I want the Ribbon Caption to change without Ribbon.Click or Ribbon.onAction just by the equivalent to the VSTO Invalidate so that the ribbon goes through a complete cycle of Getabel (I have create Ribbons up to now by writing XML files).


regards & thanks

Michael
Posted 08 Dec, 2017 12:24:38 Top
Michael Kaden




Posts: 105
Joined: 2017-11-15
Hello Andrei,

I think I have found the solution now by using the designer and creating a workbook open event. In the past, I have not worked with designers but did all the coding. So for me it is difficult to adapt.

In previous forums from you, I have read that the workbook open event will not work as when the addin is loaded, the workbook is already open and therefore the workbook open event fired will not be caught by the addin. However what I have done now seems to work?


Private Sub AdxExcelAppEvents1_WorkbookOpen(sender As Object, hostObj As Object) Handles AdxExcelAppEvents1.NewWorkbook
MsgBox("Workbook is opened")
But001.Caption = "New Caption"
End Sub

Whenever I open a workbook, I get the MSGBOX and the Ribbon caption I changed, exactly what I wanted

Anything I should watch out for?

Thank you & regards

Michael
Posted 10 Dec, 2017 09:27:20 Top
Andrei Smolin


Add-in Express team


Posts: 14583
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
history over 40 years = Fortran4


Fortan4, it was 35 years ago for me.

Michael Kaden writes:
Dim AdRib As New AddinModule
AdRib.But001.Caption = NewRes


Don't use New AddinModule. Use {project name such as MyAddin1}.AddinModule.CurrentInstance instead; this connects you to the module instance which is properly initialized. The second line in the code fragment above should invalidate the control; is it so for you?

Michael Kaden writes:
MsgBox("Workbook is opened")


We recommended *not* to use message boxes for debugging: they produce extra events that don't occur in "normal life". Use System.Diagnostics.Debig.Print and the DebugView utility instead; see http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx.

Your code is okay if you need to change a Ribbon control's caption in all workbooks.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Dec, 2017 04:00:27 Top
Michael Kaden




Posts: 105
Joined: 2017-11-15
Dear Andrei,

thank you.

1.) How do I change the caption of the ribbonTab. I get "Tab is not a member of AddInModule"

2.) Is there a method in ADX to calculate ALL for all sheet of one workbook? I did not find it within VB.net/VSTO so I used SendKeys, which is not very elegant.


regards

Michael
Posted 11 Dec, 2017 11:11:46 Top
Andrei Smolin


Add-in Express team


Posts: 14583
Joined: 2006-05-11
Hello Michael,

#1. Declare the corresponding variable public/friend or provide a public/friend method/property that modifies the visibility of that tab.

Michael Kaden writes:
Is there a method in ADX to calculate ALL for all sheet of one workbook?


Calculate All? Sorry? If this means "calculate all sheets", then the Excel object model (not ADX) allows you to loop through worksheets and calculate any or all of them; see also https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-calculate-method-excel.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Dec, 2017 04:54:16 Top
Michael Kaden




Posts: 105
Joined: 2017-11-15
Hello Andrei;

#1. Declare the corresponding variable public/friend or provide a public/friend method/property that modifies the visibility of that tab.


Don't want to make the tab visible/invisible but want to change the Caption. For example from "Calculation" to "Berechnung"

Calculate works as described by you - thank you

regards

Michael
Posted 12 Dec, 2017 07:01:02 Top
Andrei Smolin


Add-in Express team


Posts: 14583
Joined: 2006-05-11
Michael Kaden writes:
Don't want to make the tab visible/invisible but want to change the Caption. For example from "Calculation" to "Berechnung"


Mentioning the visibility was my mistake. Anyway, your options are 1) declare the component public/friendly, or 2) provide a public/friendly method that performs the change.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Dec, 2017 07:35:02 Top