locked
Excel addin automatically added button loose caption and color RRS feed

  • Question

  • I have an excel-dna addin that open an existing workbook and automatically add button controls on existing named range created on the workbook. The issue I face is that the button loose its caption and its formatting color as soon as I lock my windows session and reconnect (I am using Windows 7 with Excel 2007 + Ecxel-Dna version 0.30). Say I have a blue colored button with a caption "Process", after I lock my session and reconnect, the color is changed to grey and the label to "CommandButton1".

    Here is the code I am using:

    public class MainAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            Helper.OpenWorkbook(ExcelDnaUtil.Application, @"ButtonIssues.xlsm");
    
            var xlApp = (Application)ExcelDnaUtil.Application;
            var workbook = (Workbook)xlApp.ActiveWorkbook;
            workbook.BeforeClose += new WorkbookEvents_BeforeCloseEventHandler(workbook_BeforeClose);
    
            try
            {
                Helper.AddButton(
                    "Price",
                    "PriceRange",
                    () => MessageBox.Show(
                        string.Format("Price button on thread with ApartmentState ({0})",
                                      Thread.CurrentThread.GetApartmentState())));
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
    
        private void workbook_BeforeClose(ref bool cancel)
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    
        public void AutoClose()
        {
    
        }
    }
    
    public class Helper
    {
        public static void OpenWorkbook(object application, string workbookPath)
        {
            Workbooks workbooks = null;
    
            var xlApp = (Application)application;
    
            workbooks = xlApp.Workbooks;
            workbooks.Open(workbookPath);
        }
    
        public static void AddButton(string caption, string rangeName, System.Action action,
                              System.Drawing.Color backcolor = default(System.Drawing.Color))
        {
            var xlApp = (Application)ExcelDnaUtil.Application;
            var worksheet = (Worksheet)xlApp.ActiveWorkbook.ActiveSheet;
            object zoomSave = xlApp.ActiveWindow.Zoom;
            xlApp.ActiveWindow.Zoom = 100;
    
            Names namedRanges = null;
            Name namedRange = null;
            namedRanges = xlApp.Names;
            namedRange = namedRanges.Item(rangeName);
    
            var range = namedRange.RefersToRange;
    
            var excelButton = worksheet.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false,
                                                            Type.Missing, Type.Missing, Type.Missing, range.Left,
                                                            range.Top, range.Width, range.Height);
            xlApp.ActiveWindow.Zoom = zoomSave;
    
            //We name the command button, we will use it later
            excelButton.Name = rangeName;
    
            //In order to access the Command button object, we are using NewLateBinding class as below
            OLEObject oleButton = (OLEObject)excelButton.OLEFormat.Object;
            var commandButton = (Microsoft.Vbe.Interop.Forms.CommandButton)oleButton.Object;
    
            //Set the required properties for the command button
            commandButton.Font.Name = "Calibri";
            commandButton.FontSize = 11;
            commandButton.FontBold = true;
            commandButton.Caption = caption;
            commandButton.WordWrap = true;
            commandButton.BackColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
            commandButton.ForeColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
            commandButton.TakeFocusOnClick = false;
            commandButton.AutoSize = false;
    
            commandButton.Click += new CommandButtonEvents_ClickEventHandler(action);
            oleButton.Activate();
        }
    }
    
    Do you any idea where the issue could come from ?

    Monday, May 18, 2015 11:59 AM

All replies

  • Re:  "after I lock my session and reconnect"

    I don't do DNA and I doubt if you will find many people here who do.
    But...
    I can't see where you have saved the workbook, unless locking is dna for saving?
    '---
    Jim Cone
    Portland, Oregon USA
    Monday, May 18, 2015 1:12 PM
  • Hi lemso,

    I am trying to reproduce the issue in an VSTO application level add-in however I am failed. Here is the code for the test:

      Microsoft.Office.Tools.Excel.Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
                        System.Windows.Forms.Button button1 = new System.Windows.Forms.Button();
               button1.Text = "button1";
    
               extendedWorksheet.Controls.AddControl(button1, Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet.Range["A1"], "button1");
    

    I would suggest that you try to test in VSTO solution to see whether there this issue is relative to the add-in you were developing.

    In addition, if you have any issue with excel-dna add-ins, I would suggest that you try to get support from official support.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 19, 2015 11:49 AM
  • Hi Fei Xue,

    It seems that the issue is not linked to Excel-Dna...

    So, I have created a VSTO Add-in and added two buttons:

    • One button using he way you suggested
    • One button using the same way as in a my initial post (using the Excel COM interop) 

    I manage to reproduce the same issue (caption changed to "CommandButton1" and color that disappear) with only the button created using the Excel COM Interop just by locking my WIN7 session and reconnect after 30 seconds (please try 2 or 3 times and don't try in debug mode. Register your VSTO addin and open Excel).

    Here is the code for test (I tested with Excel 2007 Add-in, .Net framework 4.0):

    public partial class ThisAddIn
        {
            private void ThisAddIn_Startup(object sender, System.EventArgs e)
            {
                Microsoft.Office.Tools.Excel.Worksheet extendedWorksheet =
                    Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);
                System.Windows.Forms.Button button1 = new System.Windows.Forms.Button();
                button1.Text = "Button VSTO";
    
                // VSTO Button
                extendedWorksheet.Controls.AddControl(button1,
                                                      Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet.Range["B5"],
                                                      "button1");
                // Excel Interop button
                AddButton(
                        "Button COM Interop",
                        "B10:C12",
                        () => MessageBox.Show("COM Interop Button"));
            }
    
            private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
            {
            }
    
            public static void AddButton(string caption, string rangeName, System.Action action,
                                         System.Drawing.Color backcolor = default(System.Drawing.Color))
            {           
                var activeSheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
                var range = activeSheet.Range[rangeName];
    
    
                var excelButton = activeSheet.Shapes.AddOLEObject("Forms.CommandButton.1", Type.Missing, false, false,
                                                                  Type.Missing, Type.Missing, Type.Missing, range.Left,
                                                                  range.Top, range.Width, range.Height);
                excelButton.Name = rangeName;
    
                //In order to access the Command button object, we are using NewLateBinding class as below
                Excel.OLEObject oleButton = (Excel.OLEObject) excelButton.OLEFormat.Object;
                var commandButton = (Microsoft.Vbe.Interop.Forms.CommandButton) oleButton.Object;
    
                //Set the required properties for the command button
                commandButton.Font.Name = "Calibri";
                commandButton.FontSize = 11;
                commandButton.FontBold = true;
                commandButton.Caption = caption;
                commandButton.WordWrap = true;
                commandButton.BackColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
                commandButton.ForeColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
                commandButton.TakeFocusOnClick = false;
                commandButton.AutoSize = false;
    
                commandButton.Click += new CommandButtonEvents_ClickEventHandler(action);
                oleButton.Activate();
            }
    
            #region VSTO generated code
    
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(ThisAddIn_Startup);
                this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
            }
    
            #endregion
        }

    Regards

    Wednesday, May 20, 2015 9:55 AM
  • Hi Lemso,

    Thanks for the detail information for this issue, however I am still failed to reproduce the issue as you metioned, the code works well for me.

    Here is the test step:

    1. Create an Excel add-in using code above

    2. Publish it to the computer which install Office 2007 and using Windows 7

    3. Install the Excel add-in

    4. Create an spreadwheet and look the computer

    5. reconnect it

    I suggest that you update or reparir the Excel application to see whether this issue was fixed.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 21, 2015 6:21 AM
  • Hi Fei Xue,

    I rarely manage to reproduce the issue the first time I reconnect. What I noticed is that the issue start to appear the second or third reconnection waiting 30 seconds before each reconnection. 

    Another strange behaviour I have noticed:

    When I left or right click on the button after the issue appears, the initial caption and color appear and stay displayed until I move the mouse pointer outside the button region.

    Concerning your suggestion to repair the Excel application, It will be very difficult and time consuming as we have a lot of computer and we noticed the issue on each computer we made the test.

    Thanks

    Thursday, May 21, 2015 8:41 AM