windowsforms-devexpress-dot-xtraspreadsheet-dot-spreadsheetcontrol-bdd4146b.md
Occurs after the cell content was changed via the SpreadsheetControl UI.
Namespace : DevExpress.XtraSpreadsheet
Assembly : DevExpress.XtraSpreadsheet.v25.2.dll
NuGet Package : DevExpress.Win.Spreadsheet
public event CellValueChangedEventHandler CellValueChanged
Public Event CellValueChanged As CellValueChangedEventHandler
The CellValueChanged event's data class is SpreadsheetCellEventArgs. The following properties provide information specific to this event:
| Property | Description |
|---|---|
| Action | Identifies an action which caused a change of the cell value. |
| Cell | Gets the cell for which the event is fired. Inherited from SpreadsheetCellEventArgsBase. |
| ColumnIndex | Gets the index of the column that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
| Formula | Gets the formula that is currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase. |
| FormulaInvariant | Gets the formula in the invariant culture that is currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase. |
| OldFormula | Gets the cell’s previous formula. |
| OldFormulaInvariant | Gets the cell’s previous formula in the invariant culture. |
| OldValue | Gets the cell’s previous value. |
| RowIndex | Gets the index of the row that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
| SheetName | Gets the name of the worksheet that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
| Value | Gets the value currently contained in the cell. Inherited from SpreadsheetCellEventArgsBase. |
| Worksheet | Gets the worksheet that contains the cell. Inherited from SpreadsheetCellEventArgsBase. |
The CellValueChanged event can fire in the following cases:
When the cell content was modified via the in-place editor or formula bar. The Spreadsheet control closes the cell editor and commits the entered value to an active cell or selected cells when a user clicks outside the edited cell, presses ENTER, CTRL+ENTER or CTRL+SHIFT+ENTER, or the SpreadsheetControl.CloseCellEditor method is called with a parameter value other than CellEditorEnterValueMode.Cancel.
When a user pressed DELETE to clear the cell content.
When a new hyperlink was added to a cell via the Insert Hyperlink dialog, or an existing hyperlink was modified via the Edit Hyperlink dialog.
Note
The CellValueChanged event does not occur when a cell value was changed in code. Set the SpreadsheetControl.Options.Events.RaiseOnModificationsViaAPI property (WorkbookEventOptions.RaiseOnModificationsViaAPI) to true to raise this event when changes are made in code.
The CellValueChanged event does not occur after a formula was recalculated and its result was changed, even if the recalculation was triggered in the SpreadsheetControl UI.
The example below shows how to validate user input for a sample spreadsheet:
The following events are used to perform this task:
SpreadsheetControl.CellValueChanged
using DevExpress.Spreadsheet;
using DevExpress.XtraSpreadsheet;
using System.Drawing;
using System.Globalization;
using System.Windows.Forms;
// ...
public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm
{
public Form1()
{
InitializeComponent();
spreadsheetControl1.CellBeginEdit += SpreadsheetControl1_CellBeginEdit;
spreadsheetControl1.CellEndEdit += SpreadsheetControl1_CellEndEdit;
spreadsheetControl1.CellValueChanged += SpreadsheetControl1_CellValueChanged;
IWorkbook workbook = spreadsheetControl1.Document;
workbook.LoadDocument("Products.xlsx", DocumentFormat.Xlsx);
}
private CultureInfo CurrentCulture => spreadsheetControl1.Options.Culture;
private void SpreadsheetControl1_CellBeginEdit(object sender, SpreadsheetCellCancelEventArgs e)
{
// Allow users to edit cells only in the 'Unit Price' and 'Units in Stock' columns.
if (e.Cell.ColumnIndex < 2 || e.Cell.ColumnIndex > 3 || e.Cell.RowIndex < 1 || e.Cell.RowIndex > 19)
{
e.Cancel = true;
MessageBox.Show("You can edit only the 'Unit Price' and 'Units in Stock' values.", "Warning", MessageBoxButtons.OK);
}
}
private void SpreadsheetControl1_CellEndEdit(object sender, SpreadsheetCellValidatingEventArgs e)
{
// Validate a value that a user enters into a cell.
string editorText = e.EditorText;
bool validValue = e.Cell.ColumnIndex == 2 ? ValidateUnitPrice(editorText) : ValidateUnitsInStock(editorText);
if (!validValue)
{
e.Cancel = true;
MessageBox.Show("The value you entered is invalid.", "Warning", MessageBoxButtons.OK);
}
}
private bool ValidateUnitPrice(string text)
{
// Validate values in the 'Unit Price' column.
// Convert the obtained string to a Double value.
bool conversionResult = double.TryParse(text, NumberStyles.AllowDecimalPoint, CurrentCulture, out double result);
return conversionResult && result > 0;
}
private bool ValidateUnitsInStock(string text)
{
// Validate values in the 'Units in Stock' column.
// Convert the obtained string to an integer value.
return int.TryParse(text, NumberStyles.None, CurrentCulture, out int result);
}
private void SpreadsheetControl1_CellValueChanged(object sender, SpreadsheetCellEventArgs e)
{
if (e.Cell.ColumnIndex != 3)
return;
// Highlight zero values in the 'Units in Stock' column.
e.Cell.FillColor = (e.Value.NumericValue == 0) ? Color.LightPink : Color.White;
}
}
Imports DevExpress.Spreadsheet
Imports DevExpress.XtraSpreadsheet
Imports System.Drawing
Imports System.Globalization
Imports System.Windows.Forms
' ...
Partial Public Class Form1
Inherits DevExpress.XtraBars.Ribbon.RibbonForm
Public Sub New()
InitializeComponent()
AddHandler spreadsheetControl1.CellBeginEdit, AddressOf SpreadsheetControl1_CellBeginEdit
AddHandler spreadsheetControl1.CellEndEdit, AddressOf SpreadsheetControl1_CellEndEdit
AddHandler spreadsheetControl1.CellValueChanged, AddressOf SpreadsheetControl1_CellValueChanged
Dim workbook As IWorkbook = spreadsheetControl1.Document
workbook.LoadDocument("Products.xlsx", DocumentFormat.Xlsx)
End Sub
Private ReadOnly Property CurrentCulture() As CultureInfo
Get
Return spreadsheetControl1.Options.Culture
End Get
End Property
Private Sub SpreadsheetControl1_CellBeginEdit(ByVal sender As Object, ByVal e As SpreadsheetCellCancelEventArgs)
' Allow users to edit cells only in the 'Unit Price' and 'Units in Stock' columns.
If e.Cell.ColumnIndex < 2 OrElse e.Cell.ColumnIndex > 3 OrElse e.Cell.RowIndex < 1 OrElse e.Cell.RowIndex > 19 Then
e.Cancel = True
MessageBox.Show("You can edit only the 'Unit Price' and 'Units in Stock' values.", "Warning", MessageBoxButtons.OK)
End If
End Sub
Private Sub SpreadsheetControl1_CellEndEdit(ByVal sender As Object, ByVal e As SpreadsheetCellValidatingEventArgs)
' Validate a value that a user enters into a cell.
Dim editorText As String = e.EditorText
Dim validValue As Boolean = If(e.Cell.ColumnIndex = 2, ValidateUnitPrice(editorText), ValidateUnitsInStock(editorText))
If Not validValue Then
e.Cancel = True
MessageBox.Show("The value you entered is invalid.", "Warning", MessageBoxButtons.OK)
End If
End Sub
Private Function ValidateUnitPrice(ByVal text As String) As Boolean
' Validate values in the 'Unit Price' column.
' Convert the obtained string to a Double value.
Dim result As Double
Dim conversionResult As Boolean = Double.TryParse(text, NumberStyles.AllowDecimalPoint, CurrentCulture, result)
Return conversionResult AndAlso result > 0
End Function
Private Function ValidateUnitsInStock(ByVal text As String) As Boolean
' Validate values in the 'Units in Stock' column.
' Convert the obtained string to an integer value.
Dim result As Integer
Return Integer.TryParse(text, NumberStyles.None, CurrentCulture, result)
End Function
Private Sub SpreadsheetControl1_CellValueChanged(ByVal sender As Object, ByVal e As SpreadsheetCellEventArgs)
If e.Cell.ColumnIndex <> 3 Then
Return
End If
' Highlight zero values in the 'Units in Stock' column.
e.Cell.FillColor = If(e.Value.NumericValue = 0, Color.LightPink, Color.White)
End Sub
End Class
The following code snippet (auto-collected from DevExpress Examples) contains a reference to the CellValueChanged event.
Note
The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.
if (control != null)
control.CellValueChanged -= SpreadsheetControl_CellValueChanged;
control = value;
If _control IsNot Nothing Then
RemoveHandler _control.CellValueChanged, AddressOf SpreadsheetControl_CellValueChanged
End If
See Also