DataGridView formatting firiing multiple times

[Fedaykin]

I have a DataGridView populated when entering a Tab. I need to loop through the DataGridView after the Datasource is assigned to apply formatting, however the method I'm using to format the grid is firing multiple times. I've tried a few methods like .DataBindingComplete and .Sorted but the results are the same. I read a post where someone used DataGridViewCellFormattingEventArgs but I didn't understand how to handle this. I just need to find a method that reliably fires only once after the DGV is populated to do the loop.

I had to sanitize this pretty heavily so I apologize for any typos:

Private Sub Tab3Enter(sender, e) Handles TabPage3.Enter
        Call ProjTab()
End Sub
 
Dim dt As New 
 
Public Sub ProjTab()
 
        'DataGridView 3
        dt.Clear()
 
        Dim sqConQC As New SqlClient.SqlConnection(DATABASE)
        Dim sqCmdQC As New SqlClient.SqlCommand
 
        sqCmdQC.Connection = sqConQC            'create the DB connection 
        sqConQC.Open()                        'open the connection
        Dim Adapter As New SqlDataAdapter
 
 
        sql = "SELECT * FROM QuoteView WHERE DateModified >= '2019-01-01' AND Accepted = 1 AND (Released = 1 AND SONO IS NULL) OR ReleaseMod = 1 ORDER BY QuoteNo DESC"
        Adapter.SelectCommand = New SqlCommand(sql, sqConQC)
        Adapter.Fill(dt)
        sqConQC.Close()
 
        Me.DataGridView3.DataSource = dt
 
  End Sub
 
   Private Sub ColorDGV(sender As Object, e As EventArgs) Handles DataGridView1.DataBindingComplete, DataGridView2.DataBindingComplete, DataGridView3.DataBindingComplete
 
        Dim DGV As DataGridView = Nothing
 
        If Me.TabControl1.SelectedTab Is TabPage1 Then
            DGV = Me.DataGridView1
        ElseIf Me.TabControl1.SelectedTab Is TabPage2 Then
            DGV = Me.DataGridView2
        ElseIf Me.TabControl1.SelectedTab Is TabPage3 Then
            DGV = Me.DataGridView3
        End If
 
        If DGV.IsHandleCreated Then
 
            Me.ProgressBar1.Visible = True
            Dim i As Integer = DGV.Rows.Count
 
            If i > 0 Then
 
                ProgressBar1.Maximum = i
                Dim c As Integer = 0
                For Each row As DataGridViewRow In DGV.Rows
                  
                   'do lots of stuff
 
                    If ProgressBar1.Value <> ProgressBar1.Maximum Then
                        c = c + 1
                    End If
                    ProgressBar1.Value = c
                Next
            End If
        End If
        Me.ProgressBar1.Visible = False
 
    End Sub

[jmcilhinney]

I need to loop through the DataGridView after the Datasource is assigned to apply formatting

That is almost certainly not the case. There are two ways that you should be formatting a DataGridView. In simple cases, set the DefaultCellStyle.Format property of columns as required. In more complex cases, handle the CellFormatting event of the grid. If "formatting" means more than how the text is displayed, you may need to handle the CellPainting event.

[Fedaykin]

I tried handling CellFormatting and it fired more times than I could count. I tried setting breakpoints on every other sub in the form but nothing else fired. It completes the loop in ColorDGV and then fires DGVLoaded again and again.

Private Sub DGVLoaded(sender As Object, e As EventArgs) Handles DataGridView1.CellFormatting, DataGridView2.CellFormatting, DataGridView3.CellFormatting
 
        Call ColorDGV()
 
    End Sub
 
Private Sub ColorDGV()
 
        Dim DGV As DataGridView = Nothing
 
        If Me.TabControl1.SelectedTab Is TabPage1 Then
            DGV = Me.DataGridView1
        ElseIf Me.TabControl1.SelectedTab Is TabPage2 Then
            DGV = Me.DataGridView2
        ElseIf Me.TabControl1.SelectedTab Is TabPage3 Then
            DGV = Me.DataGridView3
        End If
 
        If DGV.IsHandleCreated Then
 
            Me.ProgressBar1.Visible = True
            Dim i As Integer = DGV.Rows.Count
 
            If i > 0 Then
 
                ProgressBar1.Maximum = i
                Dim c As Integer = 0
                For Each row As DataGridViewRow In DGV.Rows
                  
                   'do lots of stuff
 
                    If ProgressBar1.Value <> ProgressBar1.Maximum Then
                        c = c + 1
                    End If
                    ProgressBar1.Value = c
                Next
            End If
        End If
        Me.ProgressBar1.Visible = False
 
    End Sub

Changing my sequence to directly call ColorDGV after the DataGridView populates solves the issue when the form loads, however when changing tabs the DataGridViews populates but refuse to run the ColorDGV unless I chose to handle DataGridView3.Sorted and manually sort the column.

ColorDGV is directly called in after the DataSource is set, but it doesn't actually run until the form is fully populated and visible. Any attempt to fake out the system and do direct calls ends up firing more than once. If I put this in a button click it will work fine, but I need to automatically color the cells when the form loads.

[Fedaykin]

I tried handling DataGridView3.DataSourceChanged and the DGVColor sub definitely runs, but it does not update the colors in the DataGridView. Again, if I let the DataGridView fully populate on the tab and the form completely opens and I put this in a button click it will work.

Private Sub ColorDGV(sender As Object, e As EventArgs) Handles DataGridView1.DataSourceChanged, DataGridView2.DataSourceChanged, DataGridView3.DataSourceChanged
 
   'Loop and change Style.BackColors by criteria
 
End Sub

[Fedaykin]

I tried handling DataGridView3.DataSourceChanged and the DGVColor sub definitely runs, but it does not update the colors in the DataGridView. Again, if I let the DataGridView fully populate on the tab and the form completely opens and I put this in a button click it will work.

Private Sub ColorDGV(sender As Object, e As EventArgs) Handles DataGridView1.DataSourceChanged, DataGridView2.DataSourceChanged, DataGridView3.DataSourceChanged
 
   'Loop and change Style.BackColors by criteria
 
End Sub

[ChrisE]

I would use the Tabpage SelectedIndex
to Load and Change each DGV

something like this
the ' modMain.SQLSelectDataSet' is a reusable Function in a Module
but this should give you an idea to use the 'TabControl1.SelectedIndexChanged'

 Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, _
                                                 ByVal e As System.EventArgs) _
         Handles TabControl1.SelectedIndexChanged

        Dim objDataSet As DataSet
        objDataSet = New DataSet()

        Select Case TabControl1.SelectedIndex
            Case 0 ' User clicks on First Tab
                modMain.SQLSelectDataSet("Select * From Personal", objDataSet)
                DataGridView1.DataSource = objDataSet.Tables(0)
                'Color DGV here
            Case 1 ' User clicks on Second Tab
                modMain.SQLSelectDataSet("Select * From Customers", objDataSet)
                DataGridView2.DataSource = objDataSet.Tables(0)
                'Color DGV here

            Case 2 ' User clicks on Third Tab
                ' you get the Idea

            Case 3 ' User clicks on Fourth Tab

        End Select
    End Sub

[Fedaykin]

I was using TabPage1.Enter, do you think TabControl SelectedIndexChanged will have a different result? I'm not sure why it would but I will definitely give it a try.

[Fedaykin]

I would use the Tabpage SelectedIndex
to Load and Change each DGV

something like this
the ' modMain.SQLSelectDataSet' is a reusable Function in a Module
but this should give you an idea to use the 'TabControl1.SelectedIndexChanged'

 Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, _
                                                 ByVal e As System.EventArgs) _
         Handles TabControl1.SelectedIndexChanged

        Dim objDataSet As DataSet
        objDataSet = New DataSet()

        Select Case TabControl1.SelectedIndex
            Case 0 ' User clicks on First Tab
                modMain.SQLSelectDataSet("Select * From Personal", objDataSet)
                DataGridView1.DataSource = objDataSet.Tables(0)
                'Color DGV here
            Case 1 ' User clicks on Second Tab
                modMain.SQLSelectDataSet("Select * From Customers", objDataSet)
                DataGridView2.DataSource = objDataSet.Tables(0)
                'Color DGV here

            Case 2 ' User clicks on Third Tab
                ' you get the Idea

            Case 3 ' User clicks on Fourth Tab

        End Select
    End Sub

You saved my life!!! Why on Earth does TabControl1.SelectedIndexChanged work when TabPage1.Enter does not?? It's not logical. Actually, all of the other methods suggested SHOULD be able to be applied to this scenario but they just freak out and fire all over the place. I love VB.net but sometimes it behaves like a 2 year old on chocolate.

Thank you very much!

[ChrisE]

you want to start you code when clicking a certain Tabpage thats why= TabControl1.SelectedIndexChanged
the TabPage1.Enter will start only if you click the TabPage first and then click somewhere within that selected Tabpage

so for your needs not suitable

[Fedaykin]

Thank you for the explanation, however now I lose my formatting if I sort the DGV. I have tried changing the ColorDGV to:

Private Sub ColorDGV(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
 
 'do formatting here
 
EndSub

But this fires for every cell on every row in the DataGridView.

I need to keep my formatting if the user sorts the data.

[ChrisE]

you will have to show what and how you are coloring the DGV


just looked at you other post...

ColorDGV is directly called in after the DataSource is set, but it doesn't actually run until the form is fully populated and visible. Any attempt to fake out the system and do direct calls ends up firing more than once. If I put this in a button click it will work fine, but I need to automatically color the cells when the form loads.

so if you look at what I wrote before...
if the Button Click works as you said

 Private Sub TabControl1_SelectedIndexChanged(ByVal sender As Object, _
                                                 ByVal e As System.EventArgs) _
         Handles TabControl1.SelectedIndexChanged

        Dim objDataSet As DataSet
        objDataSet = New DataSet()

        Select Case TabControl1.SelectedIndex
            Case 0 ' User clicks on First Tab
                modMain.SQLSelectDataSet("Select * From Personal", objDataSet)
                DataGridView1.DataSource = objDataSet.Tables(0)
                'Color DGV here
                Button1.PerformClick() ' <---- color DGV
            Case 1 ' User clicks on Second Tab
                modMain.SQLSelectDataSet("Select * From Customers", objDataSet)
                DataGridView2.DataSource = objDataSet.Tables(0)
                'Color DGV here
                Button2.PerformClick() ' <---- color DGV

            Case 2 ' User clicks on Third Tab
                ' you get the Idea

            Case 3 ' User clicks on Fourth Tab

        End Select
    End Sub

[Fedaykin]

To be clear your solution worked perfectly for populating the form (without using the button click):

Private Sub TabControl1_Changed(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
        Dim DGV As DataGridView = Nothing
        Select Case TabControl1.SelectedIndex
            Case 0 ' User clicks on First Tab
                Call ProjTab()
                DGV = DataGridView3
            Case 1 ' User clicks on Second Tab
                Call RefOpen()
                DGV = DataGridView1
            Case 2 ' User clicks on Third Tab
                Call RefClosed()
                DGV = DataGridView2
        End Select
 
        Call ColorDGV(DGV)
 
    End Sub
 
 
    Private Sub ColorDGV(DGV As DataGridView)
 
        If DGV.IsHandleCreated Then
            If DGV Is DataGridView2 And Me.AllDatesCheckBox_Closed.Checked = True Then
                Exit Sub
            End If
 
            Dim col As DataGridViewColumn = DGV.Columns("QRev")
            col.DefaultCellStyle.BackColor = Color.LightGray
 
            col = DGV.Columns("QuoteNo")
            col.DefaultCellStyle.BackColor = Color.LightGray
 
            col = DGV.Columns("PType")
            col.DefaultCellStyle.Font = New Font(DGV.Font, FontStyle.Italic)
 
            Me.ProgressBar1.Visible = True
            Dim i As Integer = DGV.Rows.Count
 
            If i > 0 Then
 
                ProgressBar1.Maximum = i
                Dim c As Integer = 0
                For Each row As DataGridViewRow In DGV.Rows
                    'Read a record from the database table
                    Dim sqCon As New SqlClient.SqlConnection(MAINDB)
                    Dim sqCmd As New SqlClient.SqlCommand
                    Dim QuoteNo As String = row.Cells.Item("QuoteNo").Value
                    Dim dbDORate As Boolean = False
                    Dim dbDXRate As Boolean = False
                    Dim SageSoReq As Boolean = False
                    Dim ReleaseModBol As Boolean = False
                    sqCmd.Connection = sqCon            'create the DB connection 
                    sqCon.Open()                        'open the connection
                    sqCmd.CommandText = "SELECT DORate, DXRate, SageSoReq, ReleaseMod FROM QuoteView WHERE QuoteNo = '" & QuoteNo & "'"
 
                    Dim sqReader As SqlDataReader = sqCmd.ExecuteReader()        'execute the SQL command
                    If sqReader.HasRows Then
                        While sqReader.Read()
                            If Not IsDBNull(sqReader.Item("DORate")) Then
                                dbDORate = sqReader.Item("DORate")
                            End If
                            If Not IsDBNull(sqReader.Item("DXRate")) Then
                                dbDXRate = sqReader.Item("DXRate")
                            End If
                            If Not IsDBNull(sqReader.Item("SageSoReq")) Then
                                SageSoReq = sqReader.Item("SageSoReq")
                            End If
                            If Not IsDBNull(sqReader.Item("ReleaseMod")) Then
                                ReleaseModBol = sqReader.Item("ReleaseMod")
                            End If
                        End While
                    End If
                    sqReader.Close()         'always close the reader when you're done with it.
                    sqCon.Close()
 
                    If dbDORate = True Then
                        row.Cells("DueDate").Style.BackColor = Color.DarkOliveGreen
                        row.Cells("DueDate").Style.ForeColor = Color.White
                        If DGV Is DataGridView1 Then
                            Me.DOLbl1.Visible = True
                        End If
                    End If
                    If dbDXRate = True Then
                        row.Cells("DueDate").Style.BackColor = Color.Navy
                        row.Cells("DueDate").Style.ForeColor = Color.White
                        If DGV Is DataGridView1 Then
                            Me.DXLbl1.Visible = True
                        End If
                    End If
                    If SageSoReq = True Then
                        row.Cells("CustName").Style.BackColor = Color.LightGray
                        row.Cells("PartNum").Style.BackColor = Color.LightGray
                    End If
                    If DGV Is DataGridView3 Then
                        Dim JobID As String = QuoteNo & row.Cells("PType").Value
                        If SageSOBol(JobID) = True Then
                            row.Cells("QuoteNo").Style.BackColor = Color.LightGreen
                            row.Cells("QRev").Style.BackColor = Color.LightGreen
                            row.Cells("CustName").Style.BackColor = Color.LightGreen
                            row.Cells("PartNum").Style.BackColor = Color.LightGreen
                        End If
                    ElseIf DGV Is DataGridView1 Then
                        If row.Cells.Item("DueDate").Value < Date.Today Then
                            row.Cells.Item("DueDate").Style.BackColor = Color.Red
                        End If
                        If row.Cells.Item("DueDate").Value > Date.Today Then
                            row.Cells.Item("DueDate").Style.BackColor = Color.Honeydew
                        End If
                    End If
                    If ReleaseModBol = True Then
                        row.Cells("QuoteNo").Style.BackColor = Color.Orange
                        row.Cells("QRev").Style.BackColor = Color.Orange
                        row.Cells("CustName").Style.BackColor = Color.Orange
                        row.Cells("PartNum").Style.BackColor = Color.Orange
                    End If
                    'Check for open tickets on this Work Order
                    Try
                        Dim TagNo As String = Nothing
                        Dim dbClosed As Boolean = True
 
                        TagNo = row.Cells.Item("QuoteNO").Value
                        sqCmd = sqCon.CreateCommand()
                        sqCmd.CommandText = "SELECT Closed FROM dbo.Tagged WHERE TagNo = '" & TagNo & "'"
                        sqCon.Open()
                        Dim RetVal As Object = sqCmd.ExecuteScalar()
                        If Not IsNothing(RetVal) And Not IsDBNull(RetVal) Then
                            dbClosed = RetVal 'Returns a single value
                        End If
                        sqCon.Close()
                        If dbClosed = False Then
                            row.Cells.Item("QuoteNO").ErrorText = "Open Ticket"
                        End If
                    Catch ex As Exception
                        MessageBox.Show(ex.ToString)
                    End Try
                    If ProgressBar1.Value <> ProgressBar1.Maximum Then
                        c = c + 1
                    End If
                    ProgressBar1.Value = c
                Next
            End If
        End If
        Me.ProgressBar1.Visible = False
 
    End Sub

The problem now is that when the DataGridView is loaded and formatted with color (and the ColorDGV is only firing once using your method of TabControl1.SelectedIndexChanged!) If the user sorts a column then all of the formatting goes away and we are left with no background colors. How do we get the formatting we worked so hard for to survive a sort without having to loop back through the ColorDGV again?

[ChrisE]

your ColorDGV code is for Datagridview1 to Datagridview3
what is the point of that if you are using a Tabcontrol ?

if you have a Tabcontrol with 3 Pages and you click Tabpage2 you will see Datagridview2
you should only execute the color routine Code for that Datagridview
I mean what is the point of calling the Color routine for DGV1 and DGV2 if you clicked Tabpage3 to show DGV3

I would separate those color routines

EDIT:
consider this sample, click on any HeaderCell for sorting

Public Class Form10

    Dim tb As DataTable = New DataTable


    Private Sub Form10_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        tb.Columns.Add("ID")
        tb.Columns.Add("QtyOut")
        tb.Columns.Add("QtyIn")
        tb.Columns.Add("Stock")
        tb.Columns.Add("Balance")
        tb.Rows.Add("1", "20", "230", "400", "Confirmed")
        tb.Rows.Add("2", "6", "210", "300", "On Hold")
        tb.Rows.Add("3", "5", "220", "25", "Incomplete")
        tb.Rows.Add("4", "4", "126", "1233", "Urgent")
        DataGridView1.DataSource = tb
        ApplyFormatting()
    End Sub

    Private Sub ApplyFormatting()
        For Each row As DataGridViewRow In DataGridView1.Rows
            Select Case CStr(row.Cells(4).Value) 'based on Cell 5 color Cell 2
                Case Is = "Confirmed"
                    row.Cells(0).Style.BackColor = Color.Aquamarine
                    'or any other Cell  1 or 3 in this case
                Case Is = "On Hold"
                    row.Cells(2).Style.BackColor = Color.Pink
                Case Is = "Incomplete"
                    row.Cells(3).Style.BackColor = Color.Bisque
                Case Is = "Urgent"
                    row.Cells(2).Style.BackColor = Color.Red
            End Select
        Next
    End Sub

    Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
        ApplyFormatting()
    End Sub
End Class

[Fedaykin]

No, the ColorDGV only runs on the DataGridView that is opened, it does not run all 3. The code even has IF statements that only run based on the the DGV that is selected/opened.

I was trying not to rerun the color routine each time something is sorted, find some way to make it 'sticky'. Is there no way to avoid re-running formatting everytime there is a sort?

[ChrisE]

did you try the
DataGridView1_CellFormatting ?

 Dim tb As DataTable = New DataTable


    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        tb.Columns.Add("ID")
        tb.Columns.Add("QtyOut")
        tb.Columns.Add("QtyIn")
        tb.Columns.Add("Stock")
        tb.Columns.Add("Balance")
        tb.Rows.Add("1", "20", "230", "400", "Confirmed")
        tb.Rows.Add("2", "6", "210", "300", "On Hold")
        tb.Rows.Add("3", "5", "220", "25", "Incomplete")
        tb.Rows.Add("4", "4", "126", "1233", "Urgent")
        DataGridView1.DataSource = tb

    End Sub


    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        For Each row As DataGridViewRow In DataGridView1.Rows
            Select Case CStr(row.Cells(4).Value) 'based on Cell 5 color Cell 2
                Case Is = "Confirmed"
                    row.Cells(0).Style.BackColor = Color.Aquamarine
                    'or any other Cell  1 or 3 in this case
                Case Is = "On Hold"
                    row.Cells(2).Style.BackColor = Color.Pink
                Case Is = "Incomplete"
                    row.Cells(3).Style.BackColor = Color.Bisque
                Case Is = "Urgent"
                    row.Cells(2).Style.BackColor = Color.Red
            End Select
        Next
    End Sub

No, the ColorDGV only runs on the DataGridView that is opened, it does not run all 3. The code even has IF statements that only run based on the the DGV that is selected/opened.

what's the point of doing that if you allready use the 'TabControl1_SelectedIndexChanged'
why do you want to check which DGV to Color ?