c# excel create a button on excel worksheet

Question

I am trying to add a button on an excel worksheet. According to the example from internet, I am trying to do following code.

  using Excel = Microsoft.Office.Interop.Excel;
  using VBIDE = Microsoft.Vbe.Interop;


 private static void excelAddButtonWithVBA()
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlBook = xlApp.Workbooks.Open(@"PATH_TO_EXCEL_FILE");
Excel.Worksheet wrkSheet = xlBook.Worksheets[1];
Excel.Range range;

try
{
    //set range for insert cell
    range = wrkSheet.get_Range("A1:A1");

    //insert the dropdown into the cell
    Excel.Buttons xlButtons = wrkSheet.Buttons();
    Excel.Button xlButton = xlButtons.Add((double)range.Left, (double)range.Top, (double)range.Width, (double)range.Height);

    //set the name of the new button
    xlButton.Name = "btnDoSomething";
    xlButton.Text = "Click me!";
    xlButton.OnAction = "btnDoSomething_Click";

    buttonMacro(xlButton.Name, xlApp, xlBook, wrkSheet);
}
catch (Exception ex)
{
    Debug.WriteLine(ex.Message);
}
xlApp.Visible = true;

}

But it keeps saying "Excel does not contain Button"

What reference should I include to use Button property?

Worksheet does not have Buttons function. To use worksheet buttons, which reference do I need to include? — Meg
– Meg, Commented Nov 4, 2014 at 12:12

Lesley.Oakey · Accepted Answer · 2017-01-31 08:06:55Z

2

As far as I can tell, Excel.Buttons and Excel.Button do not exist. Instead it is suggested that the correct reference is Microsoft.Office.Tools.Excel.Controls.Button (not Microsoft.Office.Interop.Excel as you are using). This example is from the source below

    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlBook = xlApp.Workbooks.Open(@"PATH_TO_EXCEL_FILE");
    Excel.Worksheet worksheet = xlBook.Worksheets[1];

    Excel.Range selection = Globals.ThisAddIn.Application.Selection as Excel.Range;
    if (selection != null)
    {
        Microsoft.Office.Tools.Excel.Controls.Button button =
            new Microsoft.Office.Tools.Excel.Controls.Button();
        worksheet.Controls.AddControl(button, selection, "Button");
    }

Source: Adding Controls to a Worksheet at Run Time in an Application-Level Project http://msdn.microsoft.com/en-us/library/cc442817.aspx

edited Jan 31, 2017 at 8:06

answered Nov 3, 2014 at 8:34

Lesley.Oakey

3481 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Meg Over a year ago

Thank you for reply. I included Microsoft.Office.Tools.Excel.v4.0.Utilities.dll but I cannot find Microsoft.Office.Tools.Excel.Controls. I wonder what I am missing.

Meg Over a year ago

I found it! sorry I am just blind. Thank you!

Meg Over a year ago

OH. I can not find worksheet.Controls... What reference do I need to include? I declare worksheet as Microsoft.Office.Interop.Excel.Worksheet.

Lesley.Oakey Over a year ago

Sorry that I wasn't quite clear - worksheet should be an instance of your active worksheet. E.G from your example: Excel.Worksheet worksheet = xlBook.Worksheets[1]; I've updated the answer too

user8421235 Over a year ago

where can I got Microsoft.Office.Tools.Excel.Controls.Button?

Community · Accepted Answer · 2017-05-23 12:09:08Z

1

Using Lesley.Oakey's method requires you to be using the VSTO extension methods in Microsoft.Tools.Office.Excel.

If you are not using them, then you will not be able to access the Worksheet.Controls property.

Best to just use the Worksheet.Shapes container and add a new shape. There's a great post about this here:

Add excel vba code to button using c#

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Apr 5, 2015 at 23:41

stuzor

2,4511 gold badge36 silver badges49 bronze badges

Collectives™ on Stack Overflow

c# excel create a button on excel worksheet

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related