Spreadsheet gear cell with list validation selection changed event?

Question

I have list validation on a few cells and would like to shade the cell if a value is selected/changed from dropdown(filter list). For regular cells, I use EndEdit event which apparently doesn't fire in this case.

Could anyone please point me to right event to do this?

Thanks in advance!

Answer 1

There is no specific event that fires for a cell validation dropdown selection action. The WorkbookView.CellEndEdit event is solely used for "edit mode"--when a user manually types some value into a cell--and there is nothing in SpreadsheetGear API to expand edit mode to include other forms of input.

The WorkbookView.RangeChanged event will fire whenever a change is made to a cell, including via a cell validation listbox; however, many other actions trigger this event as well, such as changing a cell's NumberFormat, font formats, etc; or inputting a cell value in some other way such as pasting a value, FillDown, Clear, etc. Still, you may be able to use this event to, within some reasonable assurance, get the information you need.

Below is some sample code that demonstrates how you could detect whether RangeChanged was called from a single cell containing cell validation. Additional conditions are used to ensure to the best of our ability that the event was triggered by the user changing a value via the validation dropdown list. Again, this still isn't 100% foolproof for the reasons listed above.

private void workbookView_RangeChanged(object sender, SpreadsheetGear.Windows.Controls.RangeChangedEventArgs e)
{
    // Did this RangeChanged action affect only a single cell?
    if(e.Range.CellCount == 1)
    {
        IRange cell = e.Range;
        // Does the cell even have cell validation?
        if(cell.HasValidation)
        {
            IValidation validation = e.Range.Validation;
            // Is the validation applied to this cell of the "List" type and 
            // does the inputted value pass validation checks?
            if(validation.Type == ValidationType.List && validation.Value)
            {
                // Do something here, such as shade the cell...
                cell.Interior.Color = SpreadsheetGear.Colors.LightGray;
            }
        }
    }
}

Please see the documentation for the IValidation interface members for more information on each of the properties used above.