Function launched from ribbon with parameters from the combobox

[Paul Poupet]

Hi everybody,

 

i need your help for something simple.

I have a function which is in one class, which takes parameters. Let's say =getid(a). When you are on a selected cell, you enter it then it writes the result.

Then I have a ribbon which is in my .dna file , and on this ribbon i made a combobox with text input fields. When i am on a cell and i enter my values in the text boxes (for exemple one value "a"), i would like to lauch my function   =getid(a)

 

 

What i've tried :

In my .dna page i have :

                     <comboBox id="comboBox1"
                   enabled="true"
                  getText="GetText"
                   getLabel="GetLabel"
                   image="camera.bmp"

                    OnChange="OnChange" />

on another class that handles actions of buttons from the ribbon (not in my .dna file) :

 

if (control.Id == "comboBox1")
{
string isin;
isin = comboBox1.Text

//the following part works
ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
string lFullSheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cell);
int lIndex = lFullSheetName.IndexOf("]");
string lSheetName = lFullSheetName.Substring(lIndex + 1);
Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
int l = xlApp.ActiveCell.Row;
int c = xlApp.ActiveCell.Column;
Excel.Application app = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
Excel.Worksheet xlSheet = (Excel.Worksheet)app.ActiveWorkbook.Worksheets[lSheetName];
 
xlSheet.Cells[l , c ].FormulaLocal = DBSQL.Instance.id(isin);
 
}

 

 

 

The problem is that combobox1 is not known in the class.

 

1) is it possible to launch a function from the ribbon with parameters entered in the text boxes of a combobox of the ribbon ?

2) How ? Do I need two buttons (combobox to enter parameters, and another one that launches the function)?

3) How to launch this function?

 

Thank you in advance, i did not find anything understandable for me on google or forums.

 

Paul

[Ron]

Hi Paul,

The "OnChange" callback for the combobox type must has the following signature : void OnChange(IRibbonControl ctrl, string text)" where the second argument is the text in the combo box.

Then you can store the string somewhere (static variable, cache, etc.) for later usage.

Is that what you are looking for ?

Regards,

Ron

[Paul Poupet]

It seems to be something like this but i do not  know where to put things. Do I put the callback in my dna file?

To try to be clearer, i have my ribbon in my .dna file (see previous message for code), and i have another class (.cs) where i want to put  a function/command/... that is launched when i enter values in the combobox (ther will be 3 values) and press a button. Is it possible? (i don't care if it is a combobox or sthg else, i need a button and three text box, my button is a "use this function" and the text boxes are "with these parameters"

 

The code for the function is in the previous message, but when i put

"string isin;
isin = comboBox1.Text" , my "comboBox1 is not recognized (as it was created in my .dna file)..

 

Tell me if something is not clear.

[Ron]

I can't see why it wouldn't be possible. Perhaps the best is you show us a basic version of your code so we can tell you what to change. Otherwise I will try building it myself later when I have time. 

On my side I am developing my add-in through Visual Studio, not on the dna files directly. But I have a combo box which triggers some treatments as well.

[Paul Poupet]

In yellow are the important parts. I think the problem is only how to catch the text of the combobox when the combobox is in the Dna file. (i think but i may be wrong)

I have to finish this for 3 pm (in france), i am starting to get stressed, and i don't know if i am going in the right way.

 

 

This is my .dna file

           <DnaLibrary Name="Mette" RuntimeVersion="v4.0">
  <ExternalLibrary Path="ClassLibrary6.dll" LoadFromBytes="true" Pack="true" />

  <Reference AssemblyPath="System.Windows.Forms.dll" />

  <CustomUI>
    <customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui' loadImage='LoadImage'>
      <ribbon>
        <tabs>

          <tab id='CustomTab' label='Moulinette'>
            <group id='test' label='Test'>

                <comboBox id="comboBox1"
                   enabled="true"
                   getText="GetText"        (I do not truly understand the use of this)
                   getLabel="GetLabel"       (neither of that)
                   image="camera.bmp"
                   onChange="OnChange" />          (as i do not want any action there, maybe i should not put "on change") (i want the action/function to be launched by the button insertfunction)
                <comboBox id="comboBox2"
                   label="Insert More Text."
                   getText="GetText"
                   imageMso="TableDrawTable" />

               <button id="insertfunction" label="recherche d'un bond donné" onAction="OnButtonPressed" imageMso="FunctionWizard" showImage="true"/>

           

 <menu id="menu1" label="gestion du refresh" imageMso="Refresh" size="large">
                <button id="RefreshWorksheet" label="Refresh current Worksheet" onAction="OnButtonPressed" imageMso="Refresh" showImage="true"/>
                <menuSeparator id="m2"/>
                <button id="RefreshWorkbook" label="Refresh current Workbook" onAction="OnButtonPressed" imageMso="Refresh" showImage="true"/>
              </menu>

            </group >
          </tab>

 

 

 

 

 

And this is my .cs file :

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

 

namespace paul
{
    [ComVisible(true)]

    public class MyRibbon : ExcelRibbon
    {
        public void OnButtonPressed(IRibbonControl control)
        {
            //Recalcule la feuille
            if (control.Id == "RefreshWorksheet")
            {
                Excel.Application oXL;
                Excel.Workbook oWB;
                try
                {
                    oXL = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
                    oXL.Visible = true;
                    oWB = (Excel.Workbook)oXL.ActiveWorkbook;
                    if (oWB != null)
                    {
                        oWB.ActiveSheet.calculate();

                    }

                }
                catch { }
            }
 
            if (control.Id == "insertfunction")
            {
                string isin;
                string isin2;
               

                 isin = comboBox1.Text;
                isin2 = comboBox2.Text;

                ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
                string lFullSheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cell);
                int lIndex = lFullSheetName.IndexOf("]");
                string lSheetName = lFullSheetName.Substring(lIndex + 1);
                Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
                int l = xlApp.ActiveCell.Row;
                int c = xlApp.ActiveCell.Column;
                Excel.Application app = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
                Excel.Worksheet xlSheet = (Excel.Worksheet)app.ActiveWorkbook.Worksheets[lSheetName];

                xlSheet.Cells[l , c ].FormulaLocal = DBSQL.Instance.getValueBond(isin, isin2);
            }
    }
}

 

Thank you so much for your help Ron !

[Govert van Drimmelen]

Hi Paul,

You need to handle the ComboBox onChange callback, and then keep the text that is passed in to that handler.

Then when the button is pressed, you use your stored version of the text to proceed. So the only 'action' you take for the Combobox onChange callback is to record the text parameter.

The Office ribbon has a rather quirky API, basically everything works through the callbacks - you can't call the ribbon directly (except for InvalidateControl()).

Like the VSTO helper libraries, you can wrap it the ribbon callbacks in your own classes, to give you to effect of reading 

properties like the ComboBox content, but you'd actually be reading it from a copy in your wrapper.

It doesn't matter whether the xml markup and the handler class are in the .dna file or the compiled assembly or split as you have it. It's completely equivalent and up to you to decide whatever is easiest.

-Govert

[Paul Poupet]

Thank you for your help Govert, even if my bad english and my worse computing skills make it hard to understand.

My onchange callback has to be in the .dna file or in my .cs file ? if this is the .cs file, how do i refer to the combobox of the .dna file?

I am sorry but i do not really understand.

 

[Ron]

Dans le fichier cs : 

public void OnButtonPressed(IRibbonControl control)

Ca c'est une callback. Tu dois implementer une nouvelle fonction "void OnChange(IRibbonControl control, string text)" dont le code sauvegardera le texte dans une variable de la classe, que tu pourras relire dans la fonction OnButtonPressed.

Ainsi quand l'utilisateur modifie la combo box, ca invoquera automatiquement "OnChange" qui gardera le texte de cote. Ensuite quand l'utilisateur clique sur le bouton, "OnButtonPressed" pourra lire la variable.

Est-ce que c'est plus clair ? :)

[Paul Poupet]

OK j'essaie ca, et oui c'est beaucoup plus clair :-) je ne suis pas un king en anglais

j'essaie ca et je te dis si ca marche.

Merci 1000 fois pour ton aide.

Bonne journée

 

[Paul Poupet]

I've tried what ron said, but i have still one problem : how to reference to the combobox (in order to catch the text) :

i've tried :

 

public void OnChange1(IRibbonControl control, string text)
{
text = this.comboBox1.GetItemText(this.comboBox1.SelectedItem);
}

 

(the code for the combobox is written in my previuous message)

 

How do i fix this issue ?

 

Thanks

 

 

 

 

 

 

[Govert van Drimmelen]

Hi Paul,

 

The text in the combobox should be passed in as the second parameter (you’re calling it “text”).

 

You also need to set

onChange=”OnChange1” in the ribbon xml too.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Paul Poupet]

Hi govert,

i have already changed the "onchange" to "onchange1".

 

i am there :

        private string textValue;
        private Microsoft.Office.Core.IRibbonUI ribbon;
        public string GetText(IRibbonControl control)
        {
            return textValue;

        }
        public void OnChange1(IRibbonControl control, string text)
        {

            textValue = text;
        }

 

 

But it still does not work.

 

[Paul Poupet]

Maybe i have spoken too fast. Wait please, i think i am getting it. I will repost as soon sa possible to tell whether it worked or not

Thx again

 

Paul