Auto-completion with combo box in an Excel-Dna addin

May 14, 2015 at 9:38 AM
Hi,
My objective is to add a combo-box to an existing worksheet in my Excel-dna add-in, and use programming to make it appear in a cell that contain a data validation list (almost the same was done using VBA code here)

To that end, I have created an Excel-dna add-in that open an existing workbook.
The workbook already contains:
  1. A combo-box (created from Excel developer tab in design mode) with the Auto-completion activated
comboBox.MatchEntry = fmMatchEntry.fmMatchEntryComplete
  1. A named range that is filled dynamically and that contains the list of month names (January, ... December), with a data validation equal to "List".
var list = new System.Collections.Generic.List<string>();
                list.Add("January");
                list.Add("February");
                list.Add("March");
                list.Add("April");
                list.Add("May");
                list.Add("June");
                list.Add("July");
                list.Add("August");
                list.Add("September");
                list.Add("October");
                list.Add("November");
                list.Add("December");

                var flatList = string.Join(", ", list.ToArray());

                var activeSheet = (Worksheet)_workbook.ActiveSheet;
                var cell = (Microsoft.Office.Interop.Excel.Range)activeSheet.Cells.get_Range("ComboBoxValues");
                Validation validation = cell.Validation;
                validation.Delete();
                validation.Add(XlDVType.xlValidateList,
                    XlDVAlertStyle.xlValidAlertStop,
                    XlFormatConditionOperator.xlBetween,
                    flatList,
                    Type.Missing);
                
                validation.InCellDropdown = true;
                validation.ShowInput = true;
                validation.ShowError = true;
After opening the workbook at runtime, I fill the combobox by parsing the values from the named range (see the code below) in worksheet BeforeDoubleClick event.

I then have the following issues:
  1. I manage to display the combo-box and select a combo-box item using the mouse but the auto-completion does not work (nothing happens when I start typing).
  2. I need to press tab or enter keyword twice in order to make disappear the combo-box.
  3. Even if the named range data validation was set to XlDVAlertStyle.xlValidAlertStop, I can input an invalid value (invalid month) from the combo-box and the underlying linked cell accept the input values.
Do you have an idea on how I can solve the 3 above issues?
Thanks

You can use the code below. You just need to create a workbook that contains a named range (named "ComboBoxValues") and a combo-box using the excel developer tab in design mode.
using System;
using System.Windows.Forms;
using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop.Forms;
using Microsoft.VisualBasic.CompilerServices;
using Application = Microsoft.Office.Interop.Excel.Application;
using ComboBox = Microsoft.Vbe.Interop.Forms.ComboBox;

namespace ComboboxIssue
{
    public class MainAddin : IExcelAddIn
    {
        private static Worksheet _worksheet;
        private static ComboBox _comboBox;
        private static OLEObject _oleObject;
        private static Application _xlApp;
        private static Workbook _workbook;

        public void AutoOpen()
        {
            _xlApp = (Application)ExcelDnaUtil.Application;
            
            OpenWorkbook(@"ComboBoxIssue.xlsm");

            _workbook = (Workbook)_xlApp.ActiveWorkbook;
            _worksheet = (Worksheet)_xlApp.ActiveWorkbook.ActiveSheet;
            _workbook.BeforeClose += new WorkbookEvents_BeforeCloseEventHandler(workbook_BeforeClose);

            _oleObject = (OLEObject)_worksheet.OLEObjects("CBxValues");
            _comboBox = (ComboBox)
                        NewLateBinding.LateGet(_worksheet, null, "CBxValues", new object[0], null, null, null);

            try
            {
               var list = new System.Collections.Generic.List<string>();
                list.Add("January");
                list.Add("February");
                list.Add("March");
                list.Add("April");
                list.Add("May");
                list.Add("June");
                list.Add("July");
                list.Add("August");
                list.Add("September");
                list.Add("October");
                list.Add("November");
                list.Add("December");

                var flatList = string.Join(", ", list.ToArray());

                var activeSheet = (Worksheet)_workbook.ActiveSheet;
                var cell = (Microsoft.Office.Interop.Excel.Range)activeSheet.Cells.get_Range("ComboBoxValues");
                Validation validation = cell.Validation;
                validation.Delete();
                validation.Add(XlDVType.xlValidateList,
                    XlDVAlertStyle.xlValidAlertStop,
                    XlFormatConditionOperator.xlBetween,
                    flatList,
                    Type.Missing);
                
                validation.InCellDropdown = true;
                validation.ShowInput = true;
                validation.ShowError = true;

                RegisterEvents();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        private static void OpenWorkbook(string workbookPath)
        {
            Workbooks workbooks = null;

            //_xlApp.ErrorCheckingOptions.BackgroundChecking = false;
            //disable Excel warnings such as number stored as text

            workbooks = _xlApp.Workbooks;
            workbooks.Open(workbookPath);
        }

        private static void RegisterEvents()
        {
            _comboBox.KeyDown += new MdcComboEvents_KeyDownEventHandler(comboBox_KeyDown);
            _worksheet.BeforeDoubleClick += new DocEvents_BeforeDoubleClickEventHandler(worksheet_BeforeDoubleClick);
            _worksheet.SelectionChange += new DocEvents_SelectionChangeEventHandler(worksheet_SelectionChange);
        }

        private static void worksheet_SelectionChange(Range target)
        {
            try
            {
                _xlApp.EnableEvents = false;
                _xlApp.ScreenUpdating = true;

                if (_xlApp.CutCopyMode == XlCutCopyMode.xlCopy ||
                    _xlApp.CutCopyMode == XlCutCopyMode.xlCut)
                {
                    _xlApp.EnableEvents = true;
                    return;
                }

                _oleObject.Top = 10;
                _oleObject.Left = 10;
                _oleObject.Width = 0;
                //_oleObject.ListFillRange = string.Empty;
                _oleObject.LinkedCell = string.Empty;
                _oleObject.Visible = false;

                _comboBox.Clear();
            }
            finally
            {
                _xlApp.EnableEvents = true;
            }
        }

        private static void worksheet_BeforeDoubleClick(Range target, ref bool cancel)
        {
            if (_comboBox != null)
            {
                _comboBox.Clear();
                _comboBox.MatchEntry = fmMatchEntry.fmMatchEntryComplete;

                _oleObject.ListFillRange = string.Empty;
                _oleObject.LinkedCell = string.Empty;
                _oleObject.Visible = false;

                if (target.Validation.Type == 3)
                {
                    cancel = true;
                    _xlApp.EnableEvents = false;

                    try
                    {
                        string str = target.Validation.Formula1;

                        var values = str.Split(new char[] { ',' });
                        foreach (var value in values)
                        {
                            _comboBox.AddItem(value);
                        }

                        _oleObject.Visible = true;
                        _oleObject.Left = (double)target.Left;
                        _oleObject.Top = (double)target.Top;
                        _oleObject.Width = (double)target.Width * 1.5;
                        _oleObject.Height = (double)target.Height * 1.5;
                        //_oleObject.ListFillRange = str;
                        _oleObject.LinkedCell = target.Address;

                        _oleObject.Activate();
                        _comboBox.DropDown();
                    }
                    finally
                    {
                        _xlApp.EnableEvents = true;
                    }
                }
            }
        }

        private static void comboBox_KeyDown(ReturnInteger keyCode, short shift)
        {
            switch (keyCode.Value)
            {
                case 9: // Tab
                    _xlApp.ActiveCell.Offset[0, 1].Activate();
                    break;
                case 13: // Enter
                    _xlApp.ActiveCell.Offset[1, 0].Activate();
                    break;
            }
        }

        private void workbook_BeforeClose(ref bool cancel)
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        public void AutoClose()
        {
            
        }
    }
}
Coordinator
May 15, 2015 at 12:35 PM
Hi,

Your story story is very detailed, but I'm not sure it relates directly to Excel-DNA.
The first thing to check, is whether the behaviour is different to what you have with the VBA approach you link to.

Another place you might ask the question is on the Excel for Developers forum: https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

It sounds to me like the built-in data validation, while somewhat restricted, would be a lot less trouble.

-Govert
May 15, 2015 at 2:33 PM
Hi Govert,

Thanks for your reply.
It is probably not related to excel-dna.

In fact I have tried the VBA approach and it works well and corresponds to what I expected.
I wanted to take the same approach in my excel-dna add in without any success.

I have hundreds of values in the built-in listbox. This makes it difficult to use for selection.
Coordinator
May 16, 2015 at 11:09 AM
I'm surprised that the VBA version an the version using the COM object model from Excel-DNA is different - there's no reason why that would be.

If you email me a working version of the VBA code and a working version of your matching Excel-DNA code that behaves differently, I'm happy to have a look. You can send to govert@icon.co.za (just rename the .zip file to something like .zipfile).

-Govert
May 19, 2015 at 9:43 AM
Hi Govert,

While preparing for you a working version of my Excel-DNA add-in I noticed that there was a bug in the following line of my code
var flatList = string.Join(", ", list.ToArray());
There is a trailing space after the comma that prevented the auto completion to work correctly.
After fixing the bug, the auto completion works now perfectly.

But I still have the two following issues:
  1. I need to press tab or enter keyword twice in order to make disappear the combo-box completely (this is not the case for the VBA version). It seems to be a display issue as the control disappear when I scroll down and up to the next excel page.
  2. Even if the named range data validation was set to XlDVAlertStyle.xlValidAlertStop and comboBox MatchRequired property set to TRUE, I can input an invalid value (invalid month) from the combo-box and the underlying linked cell accept the inputted value. But I have noticed also the same behavior with the VBA version.
Since it is not related to Excel-DNA, I'll continue to check by my self the two remaining issues (unless you have any idea).
Thanks.
Coordinator
May 19, 2015 at 8:08 PM
From the way you are using the COM object model, I would not expect any difference in how the VBA behaves and how the same code implemented in an Excel-DNA add-in behaves.

For issues that you also have with VBA, you might try to post to the MSDN Excel for Developers forum https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev, or try Microsoft support.

Where you do have different behaviour, I'd be happy to have a look.

-Govert