Spread for WinForms provides several form controls for dialog sheets such as buttons, combo boxes, check boxes, spin button, list boxes, option buttons, group boxes, labels and scroll bars. With form controls, users can insert objects into worksheets that can work with data. Also, form controls make it easier for the users to interact with the cell data in the Spread worksheet.

Adding Form Controls using AddFormControl method

You can add a Form Control via the AddFormControl method of the IShape interface. This method passes the FormControl enumeration as a parameter.

Let's take a look at an example on how to add a simple button form control to the worksheet.

// Set Enhanced Shape Engine to true
fpSpread1.Features.EnhancedShapeEngine = true;
// Add Button control
fpSpread1.AsWorkbook().ActiveSheet.Cells[0, 0].Value = "Button";
var btn = fpSpread1.AsWorkbook().ActiveSheet.Shapes.AddFormControl(FormControl.Button, 80, 40, 100, 20);
btn.ControlFormat.Enabled = false;
btn.Name = "Button Shape";
btn.CanMove = Moving.None;
btn.CanSize = Sizing.Width;

' Set Enhanced Shape Engine to true

fpSpread1.Features.EnhancedShapeEngine = True

' Add Button control

fpSpread1.AsWorkbook().ActiveSheet.Cells(0, 0).Value = "Button"
Dim btn = fpSpread1.AsWorkbook().ActiveSheet.Shapes.AddFormControl(FormControl.Button, 80, 40, 100, 20)
btn.ControlFormat.Enabled = False
btn.Name = "Button Shape"
btn.CanMove = Moving.None
btn.CanSize = Sizing.Width

Similarly you can also add other controls using the AddFormControl and passing the suitable enumeration parameter for the specific control.

Adding Form Controls using IWorkSheet interface

 You can also add form controls to the worksheet using the IWorksheet interface, which represents a worksheet. For this purpose, the IWorksheet interface provides properties to add form controls.

// Button
var button = fpSpread1.AsWorkbook().ActiveSheet.Buttons.Add(0, 200, 200, 50);
button.Text = "Click Me";

' Button
Dim button = fpSpread1.AsWorkbook().ActiveSheet.Buttons.Add(0, 200, 200, 50)
button.Text = "Click Me"

var dropDown = fpSpread1.AsWorkbook().ActiveSheet.DropDowns.Add(70, 170, 105, 20);
dropDown.AddItem("Sales");
dropDown.AddItem("Tech");
dropDown.AddItem("Management");

Dim dropDown = fpSpread1.AsWorkbook().ActiveSheet.DropDowns.Add(70, 170, 105, 20)
dropDown.AddItem("Sales")
dropDown.AddItem("Tech")
dropDown.AddItem("Management")

var chkBox = fpSpread1.AsWorkbook().ActiveSheet.CheckBoxes.Add(100, 130, 100, 20);
chkBox.Text = "FootBall";

Dim chkBox = fpSpread1.AsWorkbook().ActiveSheet.CheckBoxes.Add(100, 130, 100, 20)
chkBox.Text = "FootBall"

var opt1 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(0, 75, 100, 20);
opt1.Text = "Male";
opt1.Checked = true;
var opt2 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(100, 75, 100, 20);
opt2.Text = "Female";

Dim opt1 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(0, 75, 100, 20)
opt1.Text = "Male"
opt1.Checked = True
Dim opt2 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(100, 75, 100, 20)
opt2.Text = "Female"

// ListBox
fpSpread1.AsWorkbook().ActiveSheet.Cells[7, 0].Value = "Department:";
var list = fpSpread1.AsWorkbook().ActiveSheet.ListBoxes.Add(70, 170, 105, 20);
list.MultiSelect = GrapeCity.Spreadsheet.Drawing.SelectionMode.Simple;
list.ListFillRange = "E1:E14";
list.Enabled = true;
list.Height = 100;

' ListBox
fpSpread1.AsWorkbook().ActiveSheet.Cells(7, 0).Value = "Department:"
Dim list = fpSpread1.AsWorkbook().ActiveSheet.ListBoxes.Add(70, 170, 105, 20)
list.MultiSelect = GrapeCity.Spreadsheet.Drawing.SelectionMode.Simple
list.ListFillRange = "E1:E14"
list.Enabled = True
list.Height = 100

var spinner = fpSpread1.AsWorkbook().ActiveSheet.Spinners.Add(130, 25, 50, 50);
spinner.Value = 18;
spinner.LinkedCell = "B2";

Dim spinner = fpSpread1.AsWorkbook().ActiveSheet.Spinners.Add(130, 25, 50, 50)
spinner.Value = 18
spinner.LinkedCell = "B2"

//OptionBox
var grpGender = fpSpread1.AsWorkbook().ActiveSheet.GroupBoxes.Add(0, 60, 200, 50);
grpGender.Text = "Gender";
var opt1 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(0, 75, 100, 20);
opt1.Text = "Male";
opt1.Checked = true;
var opt2 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(100, 75, 100, 20);
opt2.Text = "Female";

'OptionBox
Dim grpGender = fpSpread1.AsWorkbook().ActiveSheet.GroupBoxes.Add(0, 60, 200, 50)
grpGender.Text = "Gender"
Dim opt1 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(0, 75, 100, 20)
opt1.Text = "Male"
opt1.Checked = True
Dim opt2 = fpSpread1.AsWorkbook().ActiveSheet.OptionButtons.Add(100, 75, 100, 20)
opt2.Text = "Female"

var lbl = fpSpread1.AsWorkbook().ActiveSheet.Labels.Add(150, 170, 200, 50);
lbl.Text = "I am a label";

Dim lbl = fpSpread1.AsWorkbook().ActiveSheet.Labels.Add(150, 170, 200, 50)
lbl.Text = "I am a label"

var scrollBar = fpSpread1.AsWorkbook().ActiveSheet.ScrollBars.Add(150, 170, 250, 50);
scrollBar.Display3DShading = true; 

Dim scrollBar = fpSpread1.AsWorkbook().ActiveSheet.ScrollBars.Add(150, 170, 250, 50)
scrollBar.Display3DShading = True

Using the LinkedCell property

A major functionality of the Spread form control is that it allows you to link its value to a cell using the LinkedCell property.

The LinkedCell property sets the worksheet range linked to the control's value. For example, the code below depicts how to select an item from combo box, and how it impacts the value of A1 cell.

var dropDown = fpSpread1.AsWorkbook().ActiveSheet.DropDowns.Add(70, 170, 105, 20);
dropDown.AddItem("Sales");
dropDown.AddItem("Tech");
dropDown.AddItem("Management");
dropDown.LinkedCell = "A1";

Dim dropDown = fpSpread1.AsWorkbook().ActiveSheet.DropDowns.Add(70, 170, 105, 20)
dropDown.AddItem("Sales")
dropDown.AddItem("Tech")
dropDown.AddItem("Management")
dropDown.LinkedCell = "A1"

Using Events

Click Event

The IFormControl interface provides the Click event that occurs when the user clicks the control. For example, you can invoke the DropDown_Click event for the ComboBox for control.

The following code snippet depicts how you can click a ComboBox control and open a dropdown list of items fectched from a given cell range:

public Form1()
{
    InitializeComponent();
    fpSpread1.Features.EnhancedShapeEngine = true;
    // Adding values on cells A1 -> A5
    fpSpread1.AsWorkbook().ActiveSheet.Cells["A1"].Value = 1;
    fpSpread1.AsWorkbook().ActiveSheet.Cells["A2"].Value = 2;
    fpSpread1.AsWorkbook().ActiveSheet.Cells["A3"].Value = 3;
    fpSpread1.AsWorkbook().ActiveSheet.Cells["A4"].Value = 4;
    fpSpread1.AsWorkbook().ActiveSheet.Cells["A5"].Value = 5;
    //Add Dropdown on first cell
    var dropDown = fpSpread1.AsWorkbook().ActiveSheet.DropDowns.Add(70, 0, 100, 25);
    dropDown.ListFillRange = "A1:A5"; //Fill Dropdown list with given range
    dropDown.Click += DropDown_Click;  //Handle Click event
}
// <summary>
// Click event of dropdown
// </summary>
// <param name="sender"></param>
// <param name="e"></param>
private void DropDown_Click(object sender, EventArgs e)
{
    MessageBox.Show("Changed");
}

Class SurroundingClass
    Public Sub New()
        InitializeComponent()
        fpSpread1.Features.EnhancedShapeEngine = True
        ' Adding values on cells A1 -> A5
        fpSpread1.AsWorkbook().ActiveSheet.Cells("A1").Value = 1
        fpSpread1.AsWorkbook().ActiveSheet.Cells("A2").Value = 2
        fpSpread1.AsWorkbook().ActiveSheet.Cells("A3").Value = 3
        fpSpread1.AsWorkbook().ActiveSheet.Cells("A4").Value = 4
        fpSpread1.AsWorkbook().ActiveSheet.Cells("A5").Value = 5
        ' Add Dropdown on first cell
        Dim dropDown = fpSpread1.AsWorkbook().ActiveSheet.DropDowns.Add(70, 0, 100, 25)
        dropDown.ListFillRange = "A1:A5" 'Fill Dropdown list with given range
        dropDown.Click += AddressOf DropDown_Click 'Handle Click event
    End Sub
' <summary>
' Click event of dropdown
' </summary>
' <param name="sender"></param>
' <param name="e"></param>
  Private Sub DropDown_Click(ByVal sender As Object, ByVal e As EventArgs)
     MessageBox.Show("Changed")
  End Sub
End Class

Assigned Macros

Even though Spread cannot import and execute VBA code, users can execute their own logic if there is a function, which has the same name with the VBA function. But the function must satisfy the following conditions to be detected.

• The function signature must follow one of the two signatures given below:

  void Func();

  OR

  void Func(object sender, EventArgs e);

• The function must be declared in the form, which contains the FpSpead instance.

So, once the user opens the XLSX file, the associated VBA function name will be imported. And, when the user interacts with the control and there is an associated function, that function will be invoked.

public static void Button3_Click()
{
    MessageBox.Show("Assigned Macro Sample. Button3_Click");
}
public static void Button_click(object sender, EventArgs args)
{
    MessageBox.Show("Assigned Macro Sample Method. Button_click");
}

Class SurroundingClass
    Public Shared Sub Button3_Click()
        MessageBox.Show("Assigned Macro Sample. Button3_Click")
    End Sub
    Public Shared Sub Button_click(ByVal sender As Object, ByVal args As EventArgs)
        MessageBox.Show("Assigned Macro Sample Method. Button_click")
    End Sub
End Class

Resolve Macros

Let's say, the macro function doesn’t belong to the form, then in that case, the user is able to resolve their own macro function using the FpSpread.MacroResolve event. Also, the delegate signature must be declared as mentioned previously. For example, the sample code snippets depicts the resolve macros scenario:

private void Form1_Load(object sender, EventArgs e)
{
    //Enable enhancedshapeengine for form controls
    fpSpread1.Features.EnhancedShapeEngine = true;
    fpSpread1.OpenExcel("testmacro.xlsm");
    fpSpread1.MacroResolve += FpSpread1_MacroResolve;
}
private void FpSpread1_MacroResolve(object sender, FarPoint.Win.Spread.DelegateResolveEventArgs e)
{
    switch (e.Name)
    {
case "Button_click":
    e.Delegate = (Action<object,EventArgs>)MacroHndlers.Button_click;
    break;
case "Button3_Click":
    e.Delegate = (Action)MacroHndlers.Button3_Click;
    break;
    }
}

Class SurroundingClass
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
        ' Enable enhancedshapeengine for form controls
        fpSpread1.Features.EnhancedShapeEngine = True
        fpSpread1.OpenExcel("testmacro.xlsm")
        fpSpread1.MacroResolve += AddressOf FpSpread1_MacroResolve
    End Sub
    Private Sub FpSpread1_MacroResolve(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.DelegateResolveEventArgs)
        Select Case e.Name
            Case "Button_click"
                e.[Delegate] = CType(MacroHndlers.Button_click, Action(Of Object, EventArgs))
            Case "Button3_Click"
                e.[Delegate] = CType(MacroHndlers.Button3_Click, Action)
        End Select
    End Sub
End Class

Manipulate Form Controls at Runtime

You can invoke the Format Control dialog at runtime using the FormatDialog method of the BuiltInDialogs class. This is depicted in the code snippet below:

GrapeCity.Spreadsheet.FormControls.IScrollBar scrollBar = fpSpread1.AsWorkbook().ActiveSheet.ScrollBars.Add(100, 200, 20, 200);
BuiltInDialogs.FormatControl(fpSpread1, scrollBar);

Dim scrollBar As GrapeCity.Spreadsheet.FormControls.IScrollBar = fpSpread1.AsWorkbook().ActiveSheet.ScrollBars.Add(100, 200, 20, 200)
BuiltInDialogs.FormatControl(fpSpread1, scrollBar)

You can invoke the Format Control dialog at runtime in the following ways as well:

• Select the form control by using the "Ctrl+Click" shortcut key and press "Ctrl+1".

OR

• Right click the form control and then select the Format Control option from the context menu.

The Format Control dialog provides 4 common tabs: Size, Protection, Properties and Alt Text.

• Size tab: Change the height and width of the format control.
• Protection tab: Protect the form control using the Locked option.
• Properties tab: Change the properties of the form control.
• Alt Text tab: Add alternative text to the form control.