Working with Cells

Spread provides many options for customizing the cell appearance and interaction.

Specify the Active Cell | Display a Specified cell in the Specified Position | Work with a Range of Cells | Select Multiple Blocks using Code | Lock Cells from being Edited | Allow Cell Text to Overflow | Display Ellipsis Points | Customize Cell Borders | Set Cell Background and Text Colors | Set the Font | Specify the Color of Selected Cells | Keep Edit Mode Permanently On | Replace Text as You Edit | Spanning Cells | Merging Cells

Specify the Active Cell
The active cell is the cell that currently receives any user interaction. You can specify the active cell and you can return the coordinates of the current active cell. Usually, the active cell displays a focus rectangle. You can also hide the focus rectangle if desired. The following example sets the active cell to column 3 and row 3 (cell C3). fpSpread1.SetActiveCell 3, 3

Display a Specified cell in the Specified Position
You can scroll the spreadsheet so that the specified cell appears in the specified position, without changing the active cell. The following example scrolls the spreadsheet so that the cell E5 appears in the top-left of the spreadsheet display fpSpread1.ShowCell 3, 2, PositionUpperLeft

Work with a Range of Cells
You can define a block of cells in the spreadsheet then use the BlockMode property to set multiple properties on that block.. The following example changes the backcolor and font of a block of cells. fpSpread1.Col = 2 fpSpread1.Row = 2 fpSpread1.Col2 = 4 fpSpread1.Row2 = 3 fpSpread1.BlockMode = True fpSpread1.BackColor = RGB(0, 0, 255) fpSpread1.ForeColor = vbWhite fpSpread1.FontBold = True fpSpread1.BlockMode = False

Select Multiple Blocks using Code
Sometimes you or users might want to select several blocks of cells that are not adjacent to one another and therefore cannot be selected as one group. You can select multiple, discontiguous blocks of cells, and you can let users select multiple discontiguous blocks as well. The following example selects three blocks of cells: A1 to B2, A4 to B6, and D1 to E3 fpSpread1.AllowMultiBlocks = True fpSpread1.SetSelection 1, 1, 2, 2 fpSpread1.AddSelection 1, 4, 2, 6 fpSpread1.AddSelection 4, 1, 5, 3

Lock Cells from being Edited
You can lock cells to prevent editing. To do so, you first mark cells as locked, then tell the spreadsheet to lock the marked cells. You can remove the locked status from marked cells to edit them, and you can change cells so that they are no longer marked as locked. To indicate that cells are marked as locked, you can change their background and text colors. The following example locks the ID column from being edited and changes its' backcolor. fpSpread1.row = -1 fpSpread1.col = 1 fpSpread1.LockBackColor = RGB(192, 192, 192) fpSpread1.lock = true fpSpread1.protect = true

Allow Cell Text to Overflow
You can specify that the contents of a cell can overflow into an adjacent cell if that cell is empty. The following example allows the cells contents to overflow into the adjacent cells. fpSpread1.AllowCellOverflow = True

Display Ellipsis Points
You can allow a cell to display ellipsis points (...) when the text in the cell is too long to be displayed in the cell. The ellipsis points let the user know that more text is available in the cell. The following example displays ellipsis points for text that is too long to fit in the cell. fpSpread1.TypeEllipses = True

Customize Cell Borders
You can specify whether a cell or group of cells has a border. A group of cells can be a row, a column, a range of cells, or an entire spreadsheet. If you have a border, it can be displayed on the left, right, top, or bottom, or around all four sides of a cell or cell range. A border can be displayed as a solid, dashed, dotted, dash-dot, or dash-dot-dot line in regular and fine line weights. The following example draws a solid blue border around cell B3. fpSpread1.SetCellBorder 2, 3, 2, 3, SS_BORDER_TYPE_OUTLINE, vbBlue, CellBorderStyleSolid

Set Cell Background and Text Colors
You can change the background and text colors of a cell, a column, a row, a block of cells, or the entire spreadsheet. The following example sets the backcolor of cell B2 to Blue and the text color to yellow. fpSpread1.Row = 2 fpSpread1.Col = 2 fpSpread1.BackColor = vbBlue fpSpread1.ForeColor = vbYellow

Set the Font
You can set the font characteristics of a cell, a column, a row, a block of cells, or the entire spreadsheet. The following example sets several font characteristics for cell B2. fpSpread1.Row = 2 fpSpread1.Col = 2 fpSpread1.FontName = "Arial" fpSpread1.FontBold = True fpSpread1.FontSize = "12" fpSpread1.FontItalic = False fpSpread1.FontStrikethru = False fpSpread1.FontUnderline = True

Specify the Color of Selected Cells
By default, the spreadsheet inverts the background and text color in selected cells. You can specify the background color of the selected cells. You can also specify the color of the text in the selected cells. The following example sets the background color of the selected cells to blue and the foreground color of the selected cells to yellow fpSpread1.SelBackColor = vbBlue fpSpread1.SelForeColor = vbYellow

Replace Text as You Edit
By default, when the spreadsheet turns edit mode on, the cursor appears after the text in the active cell. If you prefer, you can have the spreadsheet select the text in the active cell. You might want to do this if you think users will want to replace the text in the cell, rather than append text to it. The following example replaces the existing text when the user starts typing in the cell. fpSpread1.EditModeReplace = True

Spanning Cells
You can combine cells to create a span of cells. This can be in the headers or in the cell data. The following example creates a row header span for rows one through 5 and a column span for columns 1 and 2. 'Create two header columns and rows fpSpread1.ColHeaderRows = 2 fpSpread1.RowHeaderCols = 2 'Col, Row , Number of Cols , Number of Rows fpSpread1.AddCellSpan SpreadHeader, 1, 1, 5 fpSpread1.AddCellSpan 1, SpreadHeader, 2, 1

Merging Cells
The control can automatically combine cells that have the same contents. You tell the control which columns and rows allow cells to be combined automatically, and any cells within that set that have the same contents are combined for you. The following example merges the data for column 1 fpSpread1.Col = 1 fpSpread1.ColMerge = MergeAlways

Working with Cells