Solved

Flexgrid Colour Question

Posted on 2004-10-10
7
225 Views
Last Modified: 2011-09-20
Hi there, having some problems with setting the background colour for different rowns in a msh flexgrid, the data is taken from an access database and what I am trying to do is change the background colour for each row, depending upon the data in the first field (skills). Then it would search through the recordset and any data that matched the skills coloumn in the flex grid would change colour to the colour provided in the recordset. eg

SKILLS            COLOUR
ARTEX            vbred
ELECTRICAL    vbblue
PLUMBER        vbyellow

So when it goes through the flexgrid any rows with ARTEX would be red.

The code im trying to use is below, not having any luck at the moment. Any help would be great

Dim lRow As Long, lCol As Long
Dim lOrgRow As Long, lOrgCol As Long
Dim lColor As Long
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database\ClientDetails.mdb;"
conn.Open

sqlstr = "Colours"

rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic

Do Until rs.EOF
MSHFlexGrid1.Redraw = False

lOrgRow = MSHFlexGrid1.Row
lOrgCol = MSHFlexGrid1.Col

For lRow = MSHFlexGrid1.FixedRows To MSHFlexGrid1.Rows - 1
MSHFlexGrid1.Row = lRow

If MSHFlexGrid1.TextMatrix(lRow, 1) = rs.Fields("Skill") Then
MSHFlexGrid1.CellBackColor = rs.Fields("Colour")
End If

For lCol = MSHFlexGrid1.FixedCols To MSHFlexGrid1.Cols - 1
MSHFlexGrid1.Col = lCol
MSHFlexGrid1.CellBackColor = rs.Fields("Colour")
Next lCol
Next lRow

MSHFlexGrid1.Row = lOrgRow
MSHFlexGrid1.Col = lOrgCol
MSHFlexGrid1.Redraw = True

Loop

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
0
Comment
Question by:StephenJnr
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12273122
Hi StephenJnr,

Past this code into a forms code and add a msh flexgrid to the form.
Option Explicit
Dim lRow, lCol
Private Sub Form_Load()
MSHFlexGrid1.TextMatrix(0, 0) = "ARTEX"
MSHFlexGrid1.TextMatrix(1, 0) = "ELECTRICAL"
MSHFlexGrid1.TextMatrix(2, 0) = "PLUMBER"

For lRow = 0 To MSHFlexGrid1.Rows - 1
   Select Case MSHFlexGrid1.TextMatrix(lRow, 0)
      Case "ARTEX"
         For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = vbRed
         Next
         lCol = 0
      Case "ELECTRICAL"
         For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = vbBlue
         Next
         lCol = 0
      Case "PLUMBER"
         For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = vbYellow
         Next
   End Select
Next
End Sub

Hope this is helpful
Bob
0
 

Author Comment

by:StephenJnr
ID: 12273139
Thanks Bob, but what im trying to do is to take any value in the recordset, say if the colour for a certain skill changes it will change the colour on the flexgrid. instead of nesting the colours, i would like to keep the option of changing them open.
0
 

Author Comment

by:StephenJnr
ID: 12273196
I tried this with limited success, I only seem to get one line of colour. I just need it to go through each line of the flexgrid and change the colour, to the one assigned to it in the recordset.

Dim lRow, lCol
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database\ClientDetails.mdb;"
conn.Open

sqlstr = "Colours order by skill"
rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic

skill = rs.Fields("Skill")
colour = rs.Fields("Colour")


For lRow = 0 To MSHFlexGrid1.Rows - 1

        If MSHFlexGrid1.TextMatrix(lRow, 1) = skill Then
            For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = colour
         Next
         lCol = 0
         End If
         Next


rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 12

Expert Comment

by:BobLamberson
ID: 12273255
StephenJnr,
> skill = rs.Fields("Skill")
> colour = rs.Fields("Colour")

You need to loop through the rs and test each skill against the value in the flexgrid then set the colour value to match.


with rs
    do
        skill = .fields("skill")
        For lRow = 0 To MSHFlexGrid1.Rows - 1

            If MSHFlexGrid1.TextMatrix(lRow, 1) = skill Then
                For lCol = 0 To MSHFlexGrid1.Cols - 1
                MSHFlexGrid1.Row = lRow
                MSHFlexGrid1.Col = lCol
                MSHFlexGrid1.CellBackColor = colour
            'Next
         lCol = 0
         End If
         Next

        rs. movenext
        if .eof then exit do
    loop
end with
Bob
0
 
LVL 12

Accepted Solution

by:
BobLamberson earned 250 total points
ID: 12273262
StephenJnr,
sorry, you should also set the colour right below skill.
Bob
0
 

Author Comment

by:StephenJnr
ID: 12273282
Thanks bob for the quck help, much Thanks

Stephen Jnr
0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12273419
StephenJnr,
glad to help - thanks for the acknowledgment
Bob
0

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question