How to pass a value to DGV Cell when its formatted to C2 format

Question

I'm struggling find a specific answer to this question, therefore asking it myself...

I have a DataGridView with columns which has the following formatting applied to it:

DGV.Columns(3).DefaultCellStyle.Format = "C2"

On the form there is a text box in which the user enters a number and that value is then entered in one of the cells in DGV:

For Each dr As DataGridViewRow In DGV.Rows
    dr.Cells("ColumnHeader").Value = TextBox.Text
Next

Before the above code is executed, this event occurs on the TextBox to format its value:

Private Sub TextBox_Leave(sender As Object, e As EventArgs) Handles TextBox.Leave
    TextBox.Text = FormatCurrency(TextBox.Text)
End Sub

The text within the TextBox is displaying correctly as a currency, but when the code to put that into a cell in the DGV executes it fails saying the value is in incorrect format.

Is DGV.Columns(3).DefaultCellStyle.Format = "C2" different format to FormatCurrency(TextBox.Text)?

Answer 1

That is all wrong. "C2" is a numeric format string, so it will only work on numbers. A String containing digit characters is not a number and a String containing currency text definitely isn't. You need to get the String from the TextBox, concert that to a number (probably Decimal for currency values) and then load that number into the grid. The grid converts that number, along with all the data, into text to display and will use your format string to do so:

dr.Cells("ColumnHeader").Value = CDec(TextBox.Text)

You would, presumably, already have validated the user input by this stage, so there's no possibility of CDec throwing an exception.

If the intent is to display the data formatted as currency in both the grid and the TextBox then you should get rid of your Leave event handler and handle the Validating and Validated events instead. The first will validate the input to ensure that it is numeric and the second will do the formatting:

Private Sub TextBox1_Validating(sender As Object, e As CancelEventArgs) Handles TextBox1.Validating
    If Not Decimal.TryParse(TextBox1.Text, NumberStyles.Currency, Nothing, Nothing) Then
        'Don't let the control lose focus when it contains invalid data.
        e.Cancel = True
    End If
End Sub

Private Sub TextBox1_Validated(sender As Object, e As EventArgs) Handles TextBox1.Validated
    TextBox1.Text = Decimal.Parse(TextBox1.Text, NumberStyles.Currency).ToString("C2")
End Sub

That code will allow the user to enter the currency symbol or not, but it will ensure that the format is currency with two decimal places once it loses focus. You would then need to allow for the formatting when copying to the grid:

dr.Cells("ColumnHeader").Value = Decimal.Parse(TextBox1.Text, NumberStyles.Currency)