Limit User input and remove special character in a DataGridView Cell

Question

I have a DataGridView with one DataGridTextBoxColumn. It is a bound to a datasource. The user can enter values such as -$12,345,678.90 (formatting is applied when tabbing out).

So what I am trying to achieve is when a user types a value like this: -$12,345,678.90, in my DataGridTextBoxColumn, I need to check the number's length, allowing a maximum length, which in my example is 10 (excluding - $ , .).

If the input number reaches a length of 10, I need to prevent the user from entering more numbers.

In the KeyDown event handler, I tried to use Regular Expression to remove the - $ , . symbols from whatever the user entered in the Cell, to find the length of the input and check whether it exceeded the maximum allowed length (e.g., 10). Like this:

//length = length value returned after performing Regex replacement with empty
If length = 10 Then
   Handled = True
End if

I also set my DataGridTextBoxColumn max length to 15 (including $ - . ,) while setting up the Grid.

The problem with this approach is that when a user tries to update a number, for example changing $12,345,678.90 to $22,345,678.90, it will treat it as a new input, so it will fail and not allow the user to update a number.

I also tried some other events such as CellEndEdit, CellValueChanged but these events are only firing when I tab out or the focus is moved from the Cell.

How can I handle this?

Answer 1

You can handle the EditingControlShowing event and set the Text of the Edit Control (it's a TextBox) to the actual value: it should be of Type Decimal.
The User can then edit the value without any formatting getting in the way.

In the CellEndEdit event handler, check whether the Value set falls within the allowed range and, in case it doesn't, revert back the edit calling CancelEdit() and set the Cell.ErrorText to provide a visual clue that something went wrong. Or show a MessageBox, or whatever fits.

To prevent exceptions in case the User enters an invalid Decimal value, the CellParsing handler rejects and reset the previous value if Decimal.TryParse() doesn't validate the input. It also shows a notification that includes the wrong input.

---

If you haven't defined the Column's ValueType and a Format in its DefaultCellStyle, do this right after you have set the [DataGridView].DataSource:
(replace ColumnName with the actual name of the Column)

DataGridView1.Columns("ColumnName").ValueType = GetType(Decimal)
DataGridView1.Columns("ColumnName").DefaultCellStyle.Format = "C" ' Or "C2"

Note:
Cell value rollback assumes that the DataGridView source of data supports notifications (e.g., a DataTable or a BindingList(Of [class]) where [class] implements INotifyPropertyChange).

---

Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
    Dim dgv = DirectCast(sender, DataGridView)
    If TypeOf e.Control Is TextBox AndAlso dgv.CurrentCell.ValueType = GetType(Decimal) Then
        e.Control.Text = CDec(dgv.CurrentCell.Value).ToString()
    End If
End Sub

Private Sub DataGridView1_CellParsing(sender As Object, e As DataGridViewCellParsingEventArgs) Handles DataGridView1.CellParsing
    If e.DesiredType IsNot GetType(Decimal) OrElse String.IsNullOrEmpty(e.Value?.ToString()) Then Return
    Dim dgv = DirectCast(sender, DataGridView)
    Dim cell = dgv(e.ColumnIndex, e.RowIndex)

    If Not Decimal.TryParse(e.Value.ToString(), Nothing) Then
        e.Value = cell.Value
        e.ParsingApplied = True
        cell.ErrorText = $"Invalid Value: {e.Value}"
    Else
        cell.ErrorText = String.Empty
    End If
End Sub

Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Dim dgv = DirectCast(sender, DataGridView)
    If dgv.Columns("ColumnName").Index = e.ColumnIndex Then
        Dim cell = dgv(e.ColumnIndex, e.RowIndex)
        Dim value = CDec(cell.Value)
        If value > 9999999999D OrElse value < -9999999999D Then
            dgv.CancelEdit()
            cell.ErrorText = "Value outside range"
        End If
    End If
End Sub