DataGridView CellFormatting or cell Painting when column is visible=false

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 ----------

Answer 1

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

Answer 2

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 ----------

Answer 3

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

Answer 4

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 ----------

Answer 5

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 ----------

Answer 6

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