Change FlexGrid Row color

[norason]

I'd like to have the FlexGrid look like a 'greenbar' report - where every even row is white and every odd row is gray.

I thought of dividing the row by 2 and if the result is even, use that to control the color, but I'm such a newbie, I don't know how to code that.

Thanks!

 

[Andrzejek]

Try this code, see if you like it:

Dim r As Integer
Dim c As Integer

With MSFlexGrid1
    .Cols = 10
    .Rows = 25
    For r = 1 To .Rows - 1
        If r Mod 2 = 0 Then
            .Row = r
            For c = 1 To .Cols - 1
                .Col = c
                .CellBackColor = [blue]vbYellow[/blue]
            Next c
        End If
    Next r
End With

Change color if you don't like Yellow

Have fun.

---- Andy

 

[norason]

I tried moving the MOD 2 = 0 to different places - no joy. Here's the code:

Dim str() As String
Dim onoff As String
Dim onoffstate As String

Dim r As Integer
Dim c As Integer

With MSFlexGrid1

.Clear
.Rows = 2
.Cols = 3
.FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
For intCount = 1 To recInfo.RecordCount
str = Split((recInfo!PLCITEM) & "", ".")
onoff = (recInfo!PLCVALUE)

If intCount Mod 2 = 0 Then
.Row = r
For c = 1 To .Cols - 1
.Col = c
.CellBackColor = vbYellow
If onoff = "1" Then
onoffstate = " ON"
Else: onoffstate = " OFF"
End If
.AddItem str(1) & " " & str(2) & vbTab & onoffstate & "" & _
vbTab & (recInfo!PLCDATETIME) & ""
recInfo.MoveNext

Next intCount
.ColWidth(0) = .Width / 2.3
.ColWidth(1) = .Width / 6.1
.ColWidth(2) = .Width / 3.1
.RowHeight(1) = 40
End With


It chokes on Next Intcount.

Thanks,

Gary

 

[norason]

I'm getting closer. This code colors all three columns first row yellow. I stepped through and it does go to for c=0 to .col etc only when the row number is even. But it doesn't color the current row yellow.

Dim str() As String
Dim onoff As String
Dim onoffstate As String

Dim r As Integer
Dim c As Integer

With MSFlexGrid1

.Clear
.Rows = 2
.Cols = 3
.FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
For intCount = 1 To recInfo.RecordCount
str = Split((recInfo!PLCITEM) & "", ".")
onoff = (recInfo!PLCVALUE)

If intCount Mod 2 = 0 Then
.Row = r
For c = 0 To .Cols - 1
.Col = c
.CellBackColor = vbYellow
Next c
End If

If onoff = "1" Then
onoffstate = " ON"
Else: onoffstate = " OFF"
End If
.AddItem str(1) & " " & str(2) & vbTab & onoffstate & "" & _
vbTab & (recInfo!PLCDATETIME) & ""
recInfo.MoveNext

Next intCount
.ColWidth(0) = .Width / 2.3
.ColWidth(1) = .Width / 6.1
.ColWidth(2) = .Width / 3.1
.RowHeight(1) = 40
End With
recInfo.Close
End Sub


Hmmm,

Gary

 

[norason]

I got it!!! It was

.Row = intCount

Sorry, and thanks!

Gary

 

[Andrzejek]

It is good that you figured it out by yourself, you will learn a lot more when you strugle this way



Have fun.

---- Andy

 

[norason]

I agree - I've learned a lot already - both from the help here, and from my own stumbling around. Now I have a problem I've spent over an hour on and I haven't figured it out yet. The color on the rows are correct - gray where it should be and white where it should be, but the data from the ACCESS file is off by one row. The first row is blank. This problem existed all along, I just didn't notice it until the colors were off. Originally the first row was 'squished' so small that I didn't notice it. Now I have it sized correctly and there is no data in it.

Here's the code:


Private Sub Class_Click()
Dim strSql As String

strSql = "SELECT * FROM Seagate7"
'WHERE ((recInfo!PLCVALUE)=1)"

recInfpen strSql, Cn

Dim str() As String
Dim onoff As String
Dim onoffstate As String
Dim r As Integer
Dim c As Integer

With MSFlexGrid1

.Clear
.Rows = 2
.Cols = 3
.ColWidth(0) = .Width / 2.3
.ColWidth(1) = .Width / 6.1
.ColWidth(2) = .Width / 3.1
.RowHeight(1) = 290
.FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
For intCount = 1 To recInfo.RecordCount
str = Split((recInfo!PLCITEM) & "", ".")
onoff = (recInfo!PLCVALUE)

If (recInfo!PLCVALUE) = "1" Then
.Row = intCount
For c = 0 To .Cols - 1
.Col = c
.CellBackColor = RGB(238, 233, 233) ' or(211, 211, 211)
Next c
Else
.Row = intCount
For c = 0 To .Cols - 1
.Col = c
.CellBackColor = vbWhite
Next c
End If

If onoff = "1" Then
onoffstate = " ON"
Else: onoffstate = " OFF"
End If
.AddItem str(1) & " " & str(2) & vbTab & onoffstate & " " & _
vbTab & (recInfo!PLCDATETIME) ' & ""
recInfo.MoveNext
Next intCount
End With
recInfo.Close
End Sub


I'm still playing with it - hopefully I'll find it soon.

Thanks,

Gary

 

[CaptainD]

Coloring cells can slow the loading so I usually set the grids redraw to false at the start and then back on after the process is done.

Just a thought

 

[Andrzejek]

Gary,

When you use [tt].AddItem[/tt] in FlexGrid, you start with one empty row which stays empty. You just Add additional rows of data.

New way of populating your Grid. Without the first empty row. Pretty much populating it cell-by-cell:

Dim r As Integer
Dim c As Integer

With MSFlexGrid1
    .Cols = 3
    [blue][b].Rows = recInfo!RecordCount[/b][/blue]
    
    .FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
    [blue]
    For r = 1 To .Rows - 1
        .Row = r
        For c = 0 To .Cols - 1[/blue]
            [red][b].TextMatrix(r, c) = "row " & r & " col " & c[/b][/red]
        [blue]Next c
    Next r[/blue]

    For r = 1 To .Rows - 1
        If r Mod 2 = 0 Then
            .Row = r
            For c = 1 To .Cols - 1
                .Col = c
                .CellBackColor = vbYellow
            Next c
        End If
    Next r
End With

Make sure your [tt]RecordCount[/tt] works OK

This way you set the number of Rows to the number of Records from your recordset, so from the very beginning you have the correct numbers of rows in your FlexGrid, and you populate it cell by cell.

Have fun.

---- Andy

 

[Andrzejek]

Good point, CaptainD

With MSFlexGrid1
    [blue].Redraw = False[/blue]
    .Cols = 3

    .........

    [blue].Redraw = True[/blue]
End With

Have fun.

---- Andy

 

[norason]

I tried populating the grid cell-by-cell, but doesn't this go thru the rows twice? If so, what happens when there are thousands of rows - won't it be slow? Secondly, eventually I want to color the rows based on the value in the row (example - if (recInfo!PLCVALUE)=1, make that row gray.

Also, for some reason this code only colors columns 2 and three, never column 1.


Private Sub Class_Click()
Dim strSql As String

strSql = "SELECT * FROM Seagate7"
'WHERE ((recInfo!PLCVALUE)=1)"

recInfpen strSql, Cn

Dim str() As String
Dim onoff As String
Dim onoffstate As String
Dim r As Integer
Dim c As Integer

With MSFlexGrid1
.Redraw = False
.Cols = 3
.Rows = intCount 'recInfo!RecordCount

.FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"

For r = 1 To .Rows - 1
.Row = r
For c = 0 To .Cols - 1
.TextMatrix(r, c) = "row " & r & " col " & c
Next c
Next r

For r = 1 To .Rows - 1
If r Mod 2 = 0 Then
.Row = r
For c = 1 To .Cols - 1
.Col = c
.CellBackColor = vbYellow
Next c
End If
Next r
.Redraw = True

End With
End Sub

Thanks,

Gary

 

[Andrzejek]

Yes, it goes twice: once when you populate the Grid with the data, second time to 'yellow' the rows. You may combine it and go just once, but thet would not make much difference. Maybe it would take some time if you would have 1000's of rows in the Grid, but why would you display that much data anyway?

You may want to go the third time to make some rows gray based on data in the cell, or combine it with the very first run. It is up to you, I just find it simpler if I split these tasks.

Column 1 (acctually it is column 0)) is a fixed column by default, if you want to color first column:

            For c = [blue][b]0[/b][/blue] To .Cols - 1
              [green]'This 0 used to be a 1[/green]
                .Col = c
                .CellBackColor = vbYellow
            Next c

Also, it will help you A LOT if you put a break point in your code and step thru code line by line to see what is going on. If you comment out the line [tt].Redraw = False[/tt] you will see what's going on in your Grid after every line of code is executing - try it, you will like it

You Open your recInfo recordset at the beginning, but you never Close it.

Have fun.

---- Andy

 

[HarleyQuinn]

If you only want to colour the row based on the value of PLCVALUE then you could do something like this (in one run as mentioned by Andy)

Dim bgcolour as Long

With MSFlexGrid1
    .Redraw = False
    .Cols = 3
    .Rows = intCount
    
    .FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
    
    For r = 1 To .Rows - 1
        .Row = r

        bgcolour = IIf(recInfo!PLCVALUE = 1, vbWhite, vbYellow)
        For c = 1 To .Cols - 1
            .TextMatrix(r, c) = "row " & r & " col " & c
            .Col = c
            .CellBackColor = bgcolour
        Next c

    recInfo.MoveNext
    Next r
    .Redraw = True

End With

That's just an example (albeit an almagamation of some of the code you have from previous posts). You can change the colours to whatever you want in the IIf statement.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[norason]

I'm very, very close to success, but...

What I see now is the FlexGrid header (in gray)-

ITEM ON-OFF DATE/TIME

Followed by a row of Yellow cells.

Then another row of Yellow cells with the first row of data with the correct data/time (7:52:13).

This if followed by all the correct yellow and white rows and correct data, just one row off.

What this amounts to is the correct background color (the first two rows should be yellow) but with data skipping the first row and starting on the second row.

I've debugged, stopping at .Row = r with the following:

IntCount = 73
RecInfo!RecordCount = 72
msFlexGrid1.Row = 1
msFlexGrid1.Rows = 2
RecInfo!PLCDATETIME = 1/30/2009 7:52:13 AM.

I've tried various values of .Rows = but nothing works. I can't get msFlexGrid1.Rows to start at row 1.

Here's the code:



Private Sub Class_Click()
Dim strSql As String
strSql = "SELECT * FROM Seagate7" 'WHERE ((recInfo!PLCVALUE)=1)"
recInfpen strSql, Cn
Dim str() As String
Dim onoff As String
Dim onoffstate As String
Dim r As Integer
Dim c As Integer
Dim bgcolour As Long
With MSFlexGrid1
.RowHeight(1) = 290
.Redraw = False
.Clear
.Rows = 2
.Cols = 3
.ColWidth(0) = .Width / 2.3
.ColWidth(1) = .Width / 6.1
.ColWidth(2) = .Width / 3.1
.FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
For r = 1 To recInfo.RecordCount
.Row = r
str = Split((recInfo!plcitem) & "", ".")
onoff = (recInfo!plcvalue)
bgcolour = IIf(recInfo!plcvalue = 1, vbYellow, vbWhite)
For c = 0 To .Cols - 1
.Col = c
.CellBackColor = bgcolour
Next c
If onoff = "1" Then
onoffstate = " ON"
Else: onoffstate = " OFF"
End If
.AddItem str(1) & " " & str(2) & vbTab & onoffstate & " " & _
vbTab & (recInfo!plcdatetime) ' & ""
recInfo.MoveNext
Next r
.Redraw = True
End With
recInfo.Close
End Sub

I've read the manual on msFlexGrid, but I still can't get it though my thick head how to move the data up to the correct row.

Thanks,

Gary

 

[Andrzejek]

What happened to your [tt].Rows = intCount[/tt]?

With MSFlexGrid1
    .Redraw = False
    .Cols = 3
    [red][b].Rows = intCount[/b][/red][green] 'recInfo!RecordCount[/green]

You show your code with an old way of [tt].Rows = 2[/tt]

    With MSFlexGrid1
        .RowHeight(1) = 290
        .Redraw = False
        .Clear
        [red][b].Rows = 2[/b][/red]

Did you see my suggestion with [tt].TextMatrix[/tt] instead of [tt].AddItem[/tt] when populating the Grid? That's the only way to populate Row 1 without having it empty.

Try this (code not tested, comment out [tt].Redraw = False[/tt] and step thru this code line by line to see what's going on):

Private Sub Class_Click()
    Dim strSql As String
    strSql = "SELECT * FROM Seagate7"    'WHERE ((recInfo!PLCVALUE)=1)"
    recInfo.Open strSql, Cn
    Dim str() As String
    Dim onoff As String
    Dim onoffstate As String
    Dim r As Integer
    Dim c As Integer
    Dim bgcolour As Long
    With MSFlexGrid1
        .RowHeight(1) = 290
        .Redraw = False
        .Clear
        [blue].Rows = recInfo.RecordCount[/blue]
        .Cols = 3
        .ColWidth(0) = .Width / 2.3
        .ColWidth(1) = .Width / 6.1
        .ColWidth(2) = .Width / 3.1
        .FormatString = "ITEM" & vbTab & " ON-OFF" & vbTab & " DATE/TIME"
            For r = 1 To recInfo.RecordCount
                .Row = r
                str = Split((recInfo!plcitem) & "", ".")
                onoff = (recInfo!plcvalue)
                bgcolour = IIf(recInfo!plcvalue = 1, vbYellow, vbWhite)
                     For c = 0 To .Cols - 1
                        .Col = c
                        .CellBackColor = bgcolour
                     Next c
                 If onoff = "1" Then
                    onoffstate = "    ON"
                    Else: onoffstate = "    OFF"
                End If
                [green]'.AddItem str(1) & " " & str(2) & vbTab & onoffstate & " " & _
                'vbTab & (recInfo!plcdatetime) ' & ""[/green]
[blue]
                .TextMatrix(r, 0) = str(1) & " " & str(2)
                .TextMatrix(r, 1) = onoffstate
                .TextMatrix(r, 2) = (recInfo!plcdatetime) & ""[/blue]

                recInfo.MoveNext
            Next r
            .Redraw = True
    End With
    recInfo.Close
End Sub

Have fun.

---- Andy

 

[norason]

Now we're cookin'!

Thanks again - the help I get at this site is outstanding!

Gary