Balagurunathan Marimuthu Balagurunathan Marimuthu - 4 months ago 52
Vb.net Question

Get selected item's tag of Ribbon ComboBox control in VSTO (VB.Net)

I have a code to list time entry (In Time, Out Time, Comments, Employee Name) information for all the employees from SQL in Excel 2010 using Excel Add-In project. Here, I wanted to move step ahead to list time entry information for selected employee from the ComboBox control (which holds the employee name and employee id in label and tag properties respectively) place in Excel Ribbon using Excel Add-In.

Here, I was unable to get the selected employee's tag (Id) from ComboBox that I have added.

Please any one help me to resolve this.

Thanks

Answer

Well this is a bit tricky but doable. First you can do that only via Ribbon XML (not via designer - at least I don't know about it)

I created very simple XML

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Ribbon_Load">
  <ribbon>
    <tabs>
      <tab idMso="TabAddIns">
        <group id="MyGroup"
               label="My Group">
          <dropDown id ="cbTest"
                    label="Test Item"
                    getItemID="GetItemID"
                    getItemLabel="GetItemLabel" 
                    getItemCount="GetItemCount" 
                    onAction="OnAction">
          </dropDown > 
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

The key part is the GetItemCount (you can name it as you want) Without this callback you will never get any callback for the getItemID or getItemLabel.

The rest is then easy, create an object that stores all the information you need, like here

public class Employee
    {
        public Employee(int id, string name)
        {
            this.ID = id;
            this.Name = name;
        }
        public int ID { get; set; }
        public string Name { get; set; }
    }

initiate the object with values like below (for easier following I put all inside the Ribbon class but this is definitely bad approach)

public class Ribbon1 : Office.IRibbonExtensibility
    {
        private Office.IRibbonUI ribbon;
        private List<Employee> _employees = new List<Employee>();

        public Ribbon1()
        {
            _employees.Add(new Employee(1, "John"));
            _employees.Add(new Employee(2, "Mark"));
            _employees.Add(new Employee(3, "Tom"));
        }
// ... rest of the code here
}

and then the callbacks (still inside the Ribbon1 class) (For list of callbacks refers here)

    public int GetItemCount(Office.IRibbonControl control)
    {
        return _employees.Count;
    }

    public string GetItemID(Office.IRibbonControl control, int index)
    {
        var employee = _employees[index];
        return employee.ID.ToString();
    }

    public string GetItemLabel(Office.IRibbonControl control, int index)
    {
        var employee = _employees[index];
        return employee.Name;
    }

    public void OnAction(Office.IRibbonControl control, string selectedId, int selectedIndex)
    {
        var selected = string.Format("{0} ({1})", _employees[selectedIndex].Name, _employees[selectedIndex].ID);
        System.Windows.Forms.MessageBox.Show(selected);
    }

Then you should see the DropDown list in your Office application, under the Add-ins tab with the tree values in this example and when you select one you should get name and ID of the employee.