How to programatically create button on Excel worksheet in C#?

Question

How can I create a button (or a shape) on an Excel worksheet using C#? I am currently doing it with the code below, but I encounter two problems:

the button gets moved when the columns to its left are resized
the button does not respond when you select or right-click it, so there is no obvious way for a user to manually delete it from the sheet after it's been auto-added

EDIT: As Lance reminds me, #2 is due to my own misunderstanding -- in order to delete form buttons, you have to use Design Mode, which you can access through the Developer tab on the ribbon bar.

Is there a better way of generating buttons that will avoid these two problems?

        // prior code is equivalent to:
        // var name = "MyButton";
        // var row = 2;
        // var buttonCol = 5; 

        Range cell = sh.Cells[row, buttonCol];
        var width = cell.Width;
        var height = 25;
        var left = cell.Left;
        var top = Math.Max(cell.Top + cell.Height - height, 0);

        var items = sh.Shapes;
        var btn = items.AddOLEObject("Forms.CommandButton.1",
            null, null, null, null,
            null, null,
            left, top, width, height);
        btn.Name = name;

        OLEObject sheetBtn = sh.OLEObjects(name);
        sheetBtn.Object.Caption = isSubmit ? "Send" : "Cancel";

Lance Roberts · Accepted Answer · 2010-10-28 20:36:17Z

2

Well you could also try to create it using the OLEObjects collection with the .Add method, it uses the same parameters as the AddOLEObject method of the Shapes collection. Though I tried the AddOLEObject code you have in VBA and it worked great. I was able to go into design mode and select the button, and the columns didn't resize it, like so:

Public Sub Test()

Dim btn As Shape

Set btn = Sheets("Sheet1").Shapes.AddOLEObject("Forms.CommandButton.1", , , , , , , 1, 1, 100, 100)

End Sub

answered Oct 28, 2010 at 20:36

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

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

1 Comment

Eric Over a year ago

D'oh -- I forgot about design mode.

Eric · Accepted Answer · 2010-10-28 21:54:53Z

To prevent the button from moving when columns before it are resized, set:

btn.Placement = XlPlacement.xlFreeFloating;

As an alternative to creating a button, you can use a shape:

        var items = sh.Shapes;
        var shape = sh.Shapes.AddShape(MsoAutoShapeType.msoShapeRectangle, left, top, width, height);
        shape.Name = name;
        shape.Placement = XlPlacement.xlFreeFloating;
        shape.TextFrame.Characters().Text = isSubmit ? "Send" : "Cancel";
        // an use this to assign a macro to it (won't happen by default as with a button)
        shape.OnAction = xl.Name + "!"+ sh.Name + "." + subName;

Note that the definition of MsoAutoShapeType is not included in the regular Office.Interop DLLs. I found it by using the COM tab of "Add Reference" and adding a reference to "Microsoft.Office 12.0 Object Library".

Collectives™ on Stack Overflow

How to programatically create button on Excel worksheet in C#?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related