VB6 Datagrid Column Formatting

[shena]

Hi all, I would like to format a column in datagrid with 2 conditions. The column must be entered with only "N/A" or date (DD-MMM-YYYY) format value.

If Me.grdDataGrid.DataFormats(10).Format <> "N/A" Then
  MsgBox "Invalid Data Entry!", vbInformation
  Exit Sub
  End If

The code above still produce "Invalid Data Entry" message though N/A is the input and I put this code under update function. How to include both conditions? Please help anyone.

[BitBlt]

First some assumptions: the underlying datatype for the column in question is text, not number or date/time. If that's not the case, read no further; what you want to do can't be done.

However, if it IS text, then you're just testing the wrong property.
"Me.grdDataGrid.DataFormats(10).Format" should be set to "dd-mm-yyyy" either at design time or at form_load time. It will never change. This will display your data as you want when the underlying data is appropriate.

When data is entered, you have to trap it in the grdDataGrid_BeforeColUpdate event so you can test the value of the cell like so:

Private Sub grdDataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
If ColIndex = 10 Then
    If Me.grdDataGrid.Columns(ColIndex).Value = "N/A" _
    Or IsDate(Me.grdDataGrid.Columns(ColIndex).Value) Then
    Else
        MsgBox "Invalid Data Entry!" & Str(ColIndex) & ": " & Me.grdDataGrid.Columns(ColIndex).Value
        Me.grdDataGrid.Columns(ColIndex).Value = OldValue
        Cancel = True
        Exit Sub
    End If
End If

End Sub

If a date is input such as "12/26/2011", the format will kick in and show it as "26-12-2011". If the user inputs "N/A" it will simply ignore the formatting and store the data as "N/A". Every other case will hoist the error message. Note that the test is case-sensitive, so you might want to UCASE() your cell value.

Note that I put a little extra in to demonstrate how to re-set the value in the cell to what it was before the edit. Not sure if you want to do this, but what the heck.

Hope this helps!

[shena]

Hi BitBit, bunch of thanks! Your suggestion worked pretty well.