Excel addin automatically added button loose caption and color

May 19, 2015 at 11:13 AM
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 + Excel-Dna version 0.30).
Say I have a blue colored button with a caption "Price", 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 ?

Thanks
May 27, 2015 at 5:26 PM
Hi elen2803,

I have modified your code slightly to get it to work in a DNA file and it works for me after waking from a sleep state. You will need to modify the path to the file you want.
<DnaLibrary RuntimeVersion="v4.0" Name="MyButtonTest" Language="C#">
  <Reference Name="System.Windows.Forms" />
  <Reference Name="System.Drawing" />
 <Reference Name="Microsoft.Vbe.Interop.Forms" />
 <Reference Name="Microsoft.Office.Interop.Excel" />
   <![CDATA[
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using Forms=System.Windows.Forms;
using System.Threading;
using System.Drawing;
using Microsoft.Vbe.Interop.Forms;

 
    public class MainAddin : IExcelAddIn
    {

    public static Microsoft.Vbe.Interop.Forms.CommandButton commandButton;
        public void AutoOpen()
        {
        //Forms.MessageBox.Show(System.Reflection.Assembly.GetCallingAssembly().FullName);
            //Forms.MessageBox.Show(System.Reflection.Assembly.GetEntryAssembly().Location);
           // Forms.MessageBox.Show(System.Reflection.Assembly.GetExecutingAssembly().Location);
            Helper.OpenWorkbook(ExcelDnaUtil.Application, @"ButtonIssues.xlsm");

            var xlApp = (Application)ExcelDnaUtil.Application;
            var workbook = (Workbook)xlApp.ActiveWorkbook;
            workbook.BeforeClose += new WorkbookEvents_BeforeCloseEventHandler(workbook_BeforeClose);

            try
            {
               commandButton =  Helper.AddButton(
                    "Price",
                    "PriceRange",
                    () => Forms.MessageBox.Show(
                        string.Format("Price button on thread with ApartmentState ({0})",
                                      Thread.CurrentThread.GetApartmentState())));
            }
            catch (Exception e)
            {
                Forms.MessageBox.Show(e.Message + " " + e.InnerException);
            }
        }

        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 Microsoft.Vbe.Interop.Forms.CommandButton 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;
            Forms.MessageBox.Show("oops");
            Names namedRanges = null;
            Name namedRange = null;
            namedRanges = xlApp.Names;
             Forms.MessageBox.Show("oops1");
            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);
                        Forms.MessageBox.Show("oops2");
            xlApp.ActiveWindow.Zoom = zoomSave;
                        Forms.MessageBox.Show("oops3");
            //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;
            Microsoft.Vbe.Interop.Forms.CommandButton 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);
            return commandButton;
          //  oleButton.Activate();
        }
    }
]]>
</DnaLibrary>
If you don't mind me saying it looks like you're trying to add functionality to an existing spreadsheet to automate a process.
Have you considered using Powershell? I have a Powershell wrapper to do exactly what you are trying to acheive, add controls to an existing or completely new excel spreadsheet. Just download XlWrapper . Run the command Invoke-XlExample to see what can be done from within Powershell.