Skip to content

Form Controls

Mats Alm edited this page Nov 2, 2023 · 10 revisions

From EPPlus 5.5 adding, removing and modifying form controls is supported.
Supported form controls are:

  • Buttons
  • Drop-Downs
  • List Boxes
  • Check Boxes
  • Radio Buttons
  • Spin Buttons
  • Scroll Bars
  • Labels
  • Group Boxes

Form controls can be linked to a cell or connected to a macro.
Form controls are added via the ExcelWorkSheet.Drawings collection using the AddControl method, or it's typed variant.
Here is an example how to add a drop-down form control and link it to a cell

//Controls are added via the worksheets drawings collection. 
//Each type has its typed method returning the specific control class. 
//Optionally you can use the AddControl method specifying the control type via the eControlType enum
var dropDown = formSheet.Drawings.AddDropDownControl("DropDown1");
dropDown.InputRange = dataSheet.Cells["A1:A2"];     //Linkes to the range of items
dropDown.LinkedCell = formSheet.Cells["C4"];        //The cell where the selected index is updated.
dropDown.SetPosition(3, 1, 1, 0);                   //Set position to row 4 with 1 pixels offset and column 2
dropDown.SetSize(453, 32);

Controls like other drawings can be grouped to make them act as one unit. Here is an example how to add a groupbox and some radio buttons and group them together:

//Add a group box and four option boxes to select room type
var grpBox = formSheet.Drawings.AddGroupBoxControl("GroupBox 1");
grpBox.Text = "Room types";
grpBox.SetPosition(5, 8, 1, 1);
grpBox.SetSize(150, 150);

var r1 = formSheet.Drawings.AddRadioButtonControl("OptionSingleRoom");
r1.Text = "Single Room";
r1.FirstButton = true;
r1.LinkedCell = formSheet.Cells["C7"];
r1.SetPosition(5, 15, 1, 5);

var r2 = formSheet.Drawings.AddRadioButtonControl("OptionDoubleRoom");
r2.Text = "Double Room";
r2.LinkedCell = formSheet.Cells["C7"];
r2.SetPosition(6, 15, 1, 5);
r2.Checked = true;

var r3 = formSheet.Drawings.AddRadioButtonControl("OptionSuperiorRoom");
r3.Text = "Superior";
r3.LinkedCell = formSheet.Cells["C7"];
r3.SetPosition(7, 15, 1, 5);

var r4 = formSheet.Drawings.AddRadioButtonControl("OptionSuite");
r4.Text = "Suite";
r4.LinkedCell = formSheet.Cells["C7"];
r4.SetPosition(8, 15, 1, 5);

//Group the group box together with the radio buttons, so they act as one unit.
//You can group drawings via the Group method on one of the drawings, here using the group box...
var grp = grpBox.Group(r1, r2, r3);     //This will group the groupbox and three of the radio buttons. You would normally include r4 here as well, but we add it in the next statement to demonstrate how group shapes work.
//...Or add them to a group drawing returned by the Group method.
grp.Drawings.Add(r4); //This will add the fourth radio button to the group

Controls can also be connected to a VBA macro. Here we connect a button to the VBA function ExportButton_Click:

var button = formSheet.Drawings.AddButtonControl("ExportButton");
button.Text = "Make Reservation";
button.Macro = "ExportButton_Click";
button.SetPosition(15, 0, 1, 0);
button.AutomaticSize = true;

See also

For more details have a look at sample 5.5 in the sample project Sample 5.5-C# or Sample 5.5-VB. Also see our wiki page on how to add VBA macros with EPPlus.

EPPlus wiki

Versions

Worksheet & Ranges

Styling

Import/Export data

Formulas and filters

Charts & Drawing objects

Tables & Pivot Tables

VBA & Protection

Clone this wiki locally