Form controls

xlsgen > overview > Form controls

Form controls in xlsgen

xlsgen lets add any of the 11 form controls in an Excel file. The form controls are :

Buttons
Check boxes
Radio buttons
Labels
Edit boxes
Spin controls
Scroll bars
List boxes
Combo boxes
Group boxes
Dialog boxes

Each form control has a number of properties in common, for instance the optional association to a VBA macro. It also includes position and size and that is for this reason that the xlsgen object model lets add a form control using either a cell/offset approach or a pixel approach.

Then each form control has its own properties, for instance a check box can have its box checked or unchecked. Here are the properties for each form control :

Button	title (regular text or rich formatted text), formula, font, alignment, position and size, protection, margins, alternate text, VBA macro association
Check box	title (regular text or rich formatted text), position and size, protection alternate text, VBA macro association, checked/unchecked, linked cell, area and border formatting
Radio button	title (regular text or rich formatted text), position and size, protection alternate text, VBA macro association, on/off, linked cell, area and border formatting
Label	title (regular text or rich formatted text), formula, position and size, protection, alternate text, VBA macro association
Edit box	title (regular text or rich formatted text), formula, position and size, protection, alternate text, VBA macro association, validation type (text, integer, float, reference, formula), multiline mode, scroll bar, password mode, linked cell
Spin control	position and size, protection, alternate text, VBA macro association, current value, minimum value, maximum value, increment value, linked cell
Scroll bar	position and size, protection, alternate text, VBA macro association, current value, minimum value, maximum value, increment value, page increment value, linked cell
List box	position and size, protection, alternate text, VBA macro association, input range, selection type, item selection(s) linked cell
Combo box	position and size, protection, alternate text, VBA macro association, input range, item selection, number of droplines, linked cell
Group box	title (regular text or rich formatted text), position and size, protection, alternate text, VBA macro association
Dialog box	title (regular text or rich formatted text), position and size, protection, alternate text, VBA macro association

Adding a form control programmatically

Java code


// create a button and attach it an existing VBA macro

XlsWorksheet worksheet = workbook.AddWorksheet( "sheet1" );

XlsFormButton button = worksheet.getFormControls().getButtons().Add("my button",2/*row1*/,2/*col1*/,5/*row2*/,6/*col2*/,0,0,0,0);

button.putVBAMacro("=myButton_Click");


XlsWorksheet worksheet2 = workbook.AddWorksheet( "sheet2" );

worksheet2.putLabel(2,7, "item1");
worksheet2.putLabel(3,7, "item2");
worksheet2.putLabel(4,7, "item3");
worksheet2.putLabel(5,7, "item4");

// create a list box and preselect two items

XlsFormListBox listbox = worksheet2.getFormControls().getListBoxes().Add(2,2,9,6,0,0,0,0);
listbox.putSelectionType(xlsgen.listboxtype_multipleselection);
listbox.putInputRange("=$G$2:$G$5");
listbox.SelectItem(3);
listbox.SelectItem(1);
listbox.putLinkedCell("=$G$10");

VB code


' create a button and attach it an existing VBA macro

Dim worksheet As IXlsWorksheet 
Set worksheet = workbook.AddWorksheet( "sheet1" )

Dim button As IXlsFormButton 
Set button = worksheet.FormControls.Buttons.Add "my button",2,2,5,6,0,0,0,0

button.VBAMacro = "=myButton_Click"


Dim worksheet2 As IXlsWorksheet 
Set worksheet2 = workbook.AddWorksheet( "sheet2" )

worksheet2.Label(2,7) = "item1"
worksheet2.Label(3,7) = "item2"
worksheet2.Label(4,7) = "item3"
worksheet2.Label(5,7) = "item4"

' create a list box and preselect two items

Dim listbox As IXlsFormListBox 
Set listbox = worksheet2.FormControls.ListBoxes.Add 2,2,9,6,0,0,0,0
listbox.SelectionType = enumListBoxSelectionType.listboxtype_multipleselection
listbox.InputRange = "=$G$2:$G$5"
listbox.SelectItem(3)
listbox.SelectItem(1)
listbox.LinkedCell = "=$G$10"

C# code


// create a button and attach it an existing VBA macro

IXlsWorksheet worksheet = workbook.AddWorksheet( "sheet1" );

IXlsFormButton button = worksheet.FormControls.Buttons.Add("my button",2/*row1*/,2/*col1*/,5/*row2*/,6/*col2*/,0,0,0,0);

button.VBAMacro = "=myButton_Click";


IXlsWorksheet worksheet2 = workbook.AddWorksheet( "sheet2" );

worksheet2.set_Label(2,7, "item1");
worksheet2.set_Label(3,7, "item2");
worksheet2.set_Label(4,7, "item3");
worksheet2.set_Label(5,7, "item4");

// create a list box and preselect two items

IXlsFormListBox listbox = worksheet2.FormControls.ListBoxes.Add(2,2,9,6,0,0,0,0);
listbox.SelectionType = enumListBoxSelectionType.listboxtype_multipleselection;
listbox.InputRange = "=$G$2:$G$5";
listbox.SelectItem(3);
listbox.SelectItem(1);
listbox.LinkedCell = "=$G$10";

C++ code


// create a button and attach it an existing VBA macro

xlsgen::IXlsWorksheetPtr worksheet = workbook->AddWorksheet( L"sheet1" );

xlsgen::IXlsFormButtonPtr button = worksheet->FormControls->Buttons->Add(L"my button",2/*row1*/,2/*col1*/,5/*row2*/,6/*col2*/,0,0,0,0);

button->VBAMacro = L"=myButton_Click";


xlsgen::IXlsWorksheetPtr worksheet2 = workbook->AddWorksheet( L"sheet2" );

worksheet2->Label[2][7] = L"item1";
worksheet2->Label[3][7] = L"item2";
worksheet2->Label[4][7] = L"item3";
worksheet2->Label[5][7] = L"item4";

// create a list box and preselect two items

xlsgen::IXlsFormListBoxPtr listbox = worksheet2->FormControls->ListBoxes->Add(2,2,9,6,0,0,0,0);
listbox->SelectionType = xlsgen::listboxtype_multipleselection;
listbox->InputRange = L"=$G$2:$G$5";
listbox->SelectItem(3);
listbox->SelectItem(1);
listbox->LinkedCell = L"=$G$10";