locked
DataGridView CellFormatting or cell Painting when column is visible=false RRS feed

  • Question

  • hello , i need some help regarding datagridview Cell Formating or cell painting.

    am binding the datagridview to some datatable and therefor the datagridview is populate my data succesfully.
    after this am making invisible some of my columns  but in the Datagriview Cell Formating event i want to check when ever of those hidden columns has this value or that value to apply some colors on the row but unfortunate this dose not work becuase could not found any of those columns because are visible false.
    i try also a loop but when i click on a column to sort the colors and any other properties i have set are gone.

    this some of my code of the 2 procedures. one for the fill of the datagridview and the other for cell formating
    if i make columns visible=true then everything works
    is there any other event where i can use ?

     Public Sub LoadTicketsByAssignee(ByVal AssigneeID As String)
            Me.DataGridView1.DataSource = Nothing
    
            TicketsBS = UIHELPEREmailClient.GetTableData("SELECT CRMTicketDetails.*, DTAllNames.MName, DTAllNames.email as CompanyEmail, CRMDepartments.CRMDepartmentName, DTEmployee.Name, DTEmployee.Email as EmployeeEmail FROM ((CRMTicketDetails LEFT JOIN CRMDepartments ON CRMTicketDetails.TicketDepartment = CRMDepartments.ID) LEFT JOIN DTAllNames ON CRMTicketDetails.TicketContactNameQBListId = DTAllNames.listid) LEFT JOIN DTEmployee ON CRMTicketDetails.TicketAssignee = DTEmployee.ListID  where TicketAssignee='" & AssigneeID & "'")
            Me.DataGridView1.DataSource = TicketsBS
    
            'Hide Columns
            Me.DataGridView1.Columns("TicketDepartment").Visible = False
            Me.DataGridView1.Columns("TicketCategory").Visible = False
            Me.DataGridView1.Columns("TicketStatus").Visible = False
            Me.DataGridView1.Columns("TicketAssignee").Visible = False
            Me.DataGridView1.Columns("TicketContactNameQBListID").Visible = False
            Me.DataGridView1.Columns("TicketSource").Visible = False
            Me.DataGridView1.Columns("TicketReceivingEmail").Visible = False
            Me.DataGridView1.Columns("TicketRemindingDate").Visible = False
            Me.DataGridView1.Columns("TicketRemindingTime").Visible = False
            Me.DataGridView1.Columns("TicketRemindingReason").Visible = False
            Me.DataGridView1.Columns("TicketQBClassListID").Visible = False
            Me.DataGridView1.Columns("TicketQBTransactionType").Visible = False
            Me.DataGridView1.Columns("TicketQBTransactionNumber").Visible = False
            Me.DataGridView1.Columns("TicketDescription").Visible = False
            Me.DataGridView1.Columns("TicketMarkedAsUnread").Visible = False
            Me.DataGridView1.Columns("TicketDateTimeStamp").Visible = False

       Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
            If Me.DataGridView1.Columns(e.ColumnIndex).Name = "TicketMarkedAsUnread" Then
                If e.Value = True Then
                    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
                Else
                    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
                End If
    
            ElseIf Me.DataGridView1.Columns(e.ColumnIndex).Name = "TicketStatus" Then
                Dim m_SelectedStyle = New DataGridViewCellStyle()
    
                If e.Value = "Pending Internal Action" Then
                    m_SelectedStyle.BackColor = Color.FromArgb(204, 255, 204)
                    '    me.datagridview1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.FromArgb(204, 255, 204)
                    Me.DataGridView1.Rows(e.RowIndex).DefaultCellStyle = m_SelectedStyle
                ElseIf e.Value = "Pending External Action" Then
                    m_SelectedStyle.BackColor = Color.FromArgb(200, 220, 235)
                    '   me.datagridview1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.FromArgb(200, 220, 235)
                    Me.DataGridView1.Rows(e.RowIndex).DefaultCellStyle = m_SelectedStyle
                ElseIf e.Value = "Closed" Then
                    Dim f As Font = Me.DataGridView1.DefaultCellStyle.Font
                    Me.DataGridView1.Rows(e.RowIndex).DefaultCellStyle.Font = New Font(f, FontStyle.Strikeout)
                    Me.DataGridView1.Rows(e.RowIndex).DefaultCellStyle.BackColor = Color.WhiteSmoke
                Else
    
                End If
    
            End If
        End Sub



    stelios ----------


    • Edited by stelios84 Friday, March 9, 2012 3:59 PM
    Friday, March 9, 2012 3:50 PM

Answers

  • ok . i have been figure it out but now it gets me to onother problem, The cell formating event nevers ends. let me give what am done:
     Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
           
    If Me.DataGridView1.Columns(e.ColumnIndex).Name = "TicketMarkedAsUnread" Then  <- in this section i was having problem becuase this column is set to visible false
               
    If e.Value = True Then
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
               
    Else
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
               
    End If
    ens dub

    Resolve with this:
    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
           
    If Me.DataGridView1.Rows(e.RowIndex).cells("TicketMarkedAsUnread").value=True    

                    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
               
    Else
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
               
    End If
    ens dub
    but with this one the cell formating event it never ends it always getting again and again to this event with out stopping. please any help?


    stelios ----------

    The following link is a VS2008 project I created to show how to change your icon. In this project selecting an item from a ListBox where each text item represents an image, pressing a button changes the value of a column of a DataTable which is the root DataSource for the DataGridView. What is not done is checking the value of a DataGridView cell yet there is no reason that this could not be done, instead the user selecting an item in the ListBox would be replaced with checking a cell value equal to something.

    To make things clear in the project, in FormLoad SimulateLoadingTableFromDatabase returns a DataTable. Next a new column is added to the DataTable as follows

    dt.Columns.Add("CompanyStatusImage", System.Type.GetType("System.Byte[]"))

    Next the image is set for each row

    For Each row As DataRow In dt.Rows
        DoIconImages(row, MyIcons)
    Next

    Using the following where the images are resources to the project

    Module ChangeRowsIcon
        Public Sub DoIconImages(ByVal row As DataRow, ByVal MyIcons As DataGridViewIcons)
            Select Case Convert.ToInt32(row("Status"))
                Case Status.OpenedForProofing
                    row("CompanyStatusImage") = MyIcons.Proof
                Case Status.Editing
                    row("CompanyStatusImage") = MyIcons.Edit
                Case Status.Rejected
                    row("CompanyStatusImage") = MyIcons.Reject
                Case Status.SentForProofing
                    row("CompanyStatusImage") = MyIcons.Sent
                Case Else
                    ' Here we are not recognizing Status Approve
                    row("CompanyStatusImage") = MyIcons.Blank
            End Select
        End Sub
    End Module

    Now if the above concerned you in that you had lots of rows to populate we can speed things up as follows by telling the UI not to update while doing the images. This could go deeper down the rabit hole by using a Try-Finally to ensure ResumeLayOut is called.

    DataGridView1.SuspendLayout()
    For Each row As DataRow In dt.Rows
        DoIconImages(row, MyIcons)
    Next
    DataGridView1.ResumeLayout()

    To change one image (as per above, using text selected in a ListBox)

    Private Sub cmdChange_Click() Handles cmdChange.Click
        Dim NewStatus As Int32
        Select Case ListBox1.Text
            Case "Editing"
                NewStatus = 10
            Case "Rejected"
                NewStatus = 20
            Case "SentForProofing"
                NewStatus = 30
            Case "OpenedForProofing"
                NewStatus = 40
            Case "Approved"
                NewStatus = 50
        End Select
        Dim Row As DataRow
        Row = bsData.DataTable.Select("ID=" & bsData.CurrentRowIdentifier)(0)
        Row("Status") = NewStatus
        DoIconImages(Row, MyIcons)
    End Sub

    Or for a dynamic method, add another column to the underlying DataTable above, add a column to the DataGridView, set the DataProperty to MyComments.

    dt.Columns.Add("MyComments", GetType(System.String))

    In CellFormatting event change the back ground color of this column if no value (which will be triggered for all rows since they are empty at startup)

    Private Sub DataGridView1_CellFormatting( _
        ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
    Handles DataGridView1.CellFormatting
        If DataGridView1.Columns(e.ColumnIndex).Name = "CommentsColumn" Then
            If String.IsNullOrEmpty(e.Value.ToString) Then
                e.CellStyle.BackColor = Color.LightCyan
            End If
        End If
    End Sub

    Once the project is running change a cell for the MyComments column and the back ground color changes with content or no content.

    Hopefully this method would work for you are the current method is ill advised.


    KSG

    • Proposed as answer by Helen Zhou Wednesday, March 14, 2012 7:23 AM
    • Marked as answer by Helen Zhou Tuesday, March 27, 2012 7:32 AM
    Monday, March 12, 2012 1:30 PM

All replies

  • Hello, The following code uses MS-NorthWind SQL-Server database (could also be MS-Access version) where there is a button to toggle the visible property of one of the DataGridView columns, in the DataGridView CellFormatting event the code checks to see if this column is visible, if so check for a specific value in another column then sets the BackColor accordingly. The good method to check the current row field data is thru a BindingSource, in this case CustomersBindingSource. In the event PositionChanged of this BindingSource we can cast Current property of the BindingSource to a DataRowView and get the value of the CompanyName column (the one we are toggling in the DataGridView visible property) be it visible or not because we are working from the data and not the UI, in this case the DataGridView. Hopefully this is of some use to you.

    VS2010 formatted code

    Public Class frmDemoToggle
        WithEvents bsParts As New BindingSource
        Private Sub frmDemoToggle_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Me.CustomersTableAdapter1.Fill(Me.NorthwndDataSet1.Customers)
        End Sub
        Private Sub cmdtoggleCompanyNameColumnVisible_Click(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) _
        Handles cmdtoggleCompanyNameColumnVisible.Click
            DataGridView1.Columns("CompanyNameDataGridViewTextBoxColumn").Visible =
                Not DataGridView1.Columns("CompanyNameDataGridViewTextBoxColumn").Visible
            ShowInfo()
        End Sub
        Private Sub DataGridView1_CellFormatting(
            ByVal sender As Object,
            ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
        Handles DataGridView1.CellFormatting
            If DataGridView1.Columns(e.ColumnIndex).Name = "ContactTitleDataGridViewTextBoxColumn" Then
                If e.Value.ToString = "Owner" Then
                    If DataGridView1.Columns("CompanyNameDataGridViewTextBoxColumn").Visible Then
                        e.CellStyle.BackColor = Color.LightSteelBlue
                    Else
                        e.CellStyle.BackColor = Color.Lime
                    End If
                End If
            End If
        End Sub
        Private Sub CustomersBindingSource_PositionChanged( _
            ByVal sender As Object,
            ByVal e As System.EventArgs) _
        Handles CustomersBindingSource.PositionChanged
            ShowInfo()
        End Sub
        Private Sub ShowInfo()
            If CustomersBindingSource.Current IsNot Nothing Then
                Console.WriteLine("Current Company: [{0}] Column Visible: {1}", _
                                  CType(CustomersBindingSource.Current, DataRowView).Item("CompanyName"), _
                                  If(DataGridView1.Columns("CompanyNameDataGridViewTextBoxColumn").Visible,
                                     "Yes",
                                     "No"
                                )
                            )
            End If
        End Sub
        Private Sub frmDemoToggle_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
            ShowInfo()
        End Sub
    End Class


    KSG

    Friday, March 9, 2012 6:28 PM
  • hello , thank you for your time to reply me,

    my skills are not very well in programing , but if i understant correctly of what you advice me , is instead of doing that in the datagridview cell formating event to do it in the bindingsource position change event ? am i correct ? , and from the Binding source position change event to handle the object as a datagridrow  and apply the appropriate properties to that row ? of what i understant so far is something like this code below .

    withEvents DataBinsingSource as new bindingSource

    Private sub LoadData
    DataBinsingSource =some data to be filled on that bindingsource
    me.datagridview1.datasource=databindingSource
    end sub

    private sub BindingSourcePositionChange(sender as object,e as systemeventArg..) handles databindingSource.positionchange
    How i can perform here the code? to apply that in the datagridview rows?
    if databindingSource.Current isNot Nothing then
    how i can set the row color for example to green depending of some value of my databindingSource.current("SomefieldToCheck")

    End sub


    stelios ----------

    Saturday, March 10, 2012 1:20 AM
  • hello , thank you for your time to reply me,

    my skills are not very well in programing , but if i understant correctly of what you advice me , is instead of doing that in the datagridview cell formating event to do it in the bindingsource position change event ? am i correct ? , and from the Binding source position change event to handle the object as a datagridrow  and apply the appropriate properties to that row ? of what i understant so far is something like this code below .

    withEvents DataBinsingSource as new bindingSource

    Private sub LoadData
    DataBinsingSource =some data to be filled on that bindingsource
    me.datagridview1.datasource=databindingSource
    end sub

    private sub BindingSourcePositionChange(sender as object,e as systemeventArg..) handles databindingSource.positionchange
    How i can perform here the code? to apply that in the datagridview rows?
    if databindingSource.Current isNot Nothing then
    how i can set the row color for example to green depending of some value of my databindingSource.current("SomefieldToCheck")

    End sub


    stelios ----------

    Hello,

    I thought you where already using a BindingSource (which I am now seeing is not the case) as you indicating you are binding to a DataGridView your data.

    If a column in the DataGridView is visible or not can be determined as shown in my first reply also shown below. I did notice you are doing a bit more with formatting which I did not to keep things simple yet can do more heavy stuff with cell formatting.

    If DataGridView1.Columns("CompanyNameDataGridViewTextBoxColumn").Visible Then
        e.CellStyle.BackColor = Color.LightSteelBlue
    Else
        e.CellStyle.BackColor = Color.Lime
    End If

    The code shown in the BindingSource PositionChanged event is to show that you can learn about data of the current row in the DataGridView. I was going to show also that you could use CellEnter and CellLeave event of the DataGridView as yet another alternate to get data from moving from cell to cell in the DataGridView also.

    So with this known perhaps you can indicate in a brief summary w/o code exactly what the problem is in CellFormatting and visible/non-visible columns.


    KSG

    Saturday, March 10, 2012 2:07 AM
  • hello, the problem is that when the datagridview cell formating event is fire . it can handle only the cells that are visible true .for any columns that are visible false you can not get the value of those cells. and here is my problem becuase i need to check one of those cells that are visible false to get the value and i cannot do that.
    as i show in my 2nd code above the columns that am checking are visible false. and i canot get the values


    stelios ----------

    Monday, March 12, 2012 7:56 AM
  • ok . i have been figure it out but now it gets me to onother problem, The cell formating event nevers ends. let me give what am done:
     Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
           
    If Me.DataGridView1.Columns(e.ColumnIndex).Name = "TicketMarkedAsUnread" Then  <- in this section i was having problem becuase this column is set to visible false
               
    If e.Value = True Then
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
               
    Else
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
               
    End If
    ens dub

    Resolve with this:
    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
           
    If Me.DataGridView1.Rows(e.RowIndex).cells("TicketMarkedAsUnread").value=True    

                    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
               
    Else
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
               
    End If
    ens dub
    but with this one the cell formating event it never ends it always getting again and again to this event with out stopping. please any help?


    stelios ----------

    Monday, March 12, 2012 8:15 AM
  • ok . i have been figure it out but now it gets me to onother problem, The cell formating event nevers ends. let me give what am done:
     Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
           
    If Me.DataGridView1.Columns(e.ColumnIndex).Name = "TicketMarkedAsUnread" Then  <- in this section i was having problem becuase this column is set to visible false
               
    If e.Value = True Then
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
               
    Else
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
               
    End If
    ens dub

    Resolve with this:
    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
           
    If Me.DataGridView1.Rows(e.RowIndex).cells("TicketMarkedAsUnread").value=True    

                    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(0)
               
    Else
                   
    Me.DataGridView1.Rows(e.RowIndex).Cells("Column1").Value = MyImageList.Images(2)
               
    End If
    ens dub
    but with this one the cell formating event it never ends it always getting again and again to this event with out stopping. please any help?


    stelios ----------

    The following link is a VS2008 project I created to show how to change your icon. In this project selecting an item from a ListBox where each text item represents an image, pressing a button changes the value of a column of a DataTable which is the root DataSource for the DataGridView. What is not done is checking the value of a DataGridView cell yet there is no reason that this could not be done, instead the user selecting an item in the ListBox would be replaced with checking a cell value equal to something.

    To make things clear in the project, in FormLoad SimulateLoadingTableFromDatabase returns a DataTable. Next a new column is added to the DataTable as follows

    dt.Columns.Add("CompanyStatusImage", System.Type.GetType("System.Byte[]"))

    Next the image is set for each row

    For Each row As DataRow In dt.Rows
        DoIconImages(row, MyIcons)
    Next

    Using the following where the images are resources to the project

    Module ChangeRowsIcon
        Public Sub DoIconImages(ByVal row As DataRow, ByVal MyIcons As DataGridViewIcons)
            Select Case Convert.ToInt32(row("Status"))
                Case Status.OpenedForProofing
                    row("CompanyStatusImage") = MyIcons.Proof
                Case Status.Editing
                    row("CompanyStatusImage") = MyIcons.Edit
                Case Status.Rejected
                    row("CompanyStatusImage") = MyIcons.Reject
                Case Status.SentForProofing
                    row("CompanyStatusImage") = MyIcons.Sent
                Case Else
                    ' Here we are not recognizing Status Approve
                    row("CompanyStatusImage") = MyIcons.Blank
            End Select
        End Sub
    End Module

    Now if the above concerned you in that you had lots of rows to populate we can speed things up as follows by telling the UI not to update while doing the images. This could go deeper down the rabit hole by using a Try-Finally to ensure ResumeLayOut is called.

    DataGridView1.SuspendLayout()
    For Each row As DataRow In dt.Rows
        DoIconImages(row, MyIcons)
    Next
    DataGridView1.ResumeLayout()

    To change one image (as per above, using text selected in a ListBox)

    Private Sub cmdChange_Click() Handles cmdChange.Click
        Dim NewStatus As Int32
        Select Case ListBox1.Text
            Case "Editing"
                NewStatus = 10
            Case "Rejected"
                NewStatus = 20
            Case "SentForProofing"
                NewStatus = 30
            Case "OpenedForProofing"
                NewStatus = 40
            Case "Approved"
                NewStatus = 50
        End Select
        Dim Row As DataRow
        Row = bsData.DataTable.Select("ID=" & bsData.CurrentRowIdentifier)(0)
        Row("Status") = NewStatus
        DoIconImages(Row, MyIcons)
    End Sub

    Or for a dynamic method, add another column to the underlying DataTable above, add a column to the DataGridView, set the DataProperty to MyComments.

    dt.Columns.Add("MyComments", GetType(System.String))

    In CellFormatting event change the back ground color of this column if no value (which will be triggered for all rows since they are empty at startup)

    Private Sub DataGridView1_CellFormatting( _
        ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
    Handles DataGridView1.CellFormatting
        If DataGridView1.Columns(e.ColumnIndex).Name = "CommentsColumn" Then
            If String.IsNullOrEmpty(e.Value.ToString) Then
                e.CellStyle.BackColor = Color.LightCyan
            End If
        End If
    End Sub

    Once the project is running change a cell for the MyComments column and the back ground color changes with content or no content.

    Hopefully this method would work for you are the current method is ill advised.


    KSG

    • Proposed as answer by Helen Zhou Wednesday, March 14, 2012 7:23 AM
    • Marked as answer by Helen Zhou Tuesday, March 27, 2012 7:32 AM
    Monday, March 12, 2012 1:30 PM