Solved

Flexgrid question for cliffab

Posted on 1998-07-20
7
239 Views
Last Modified: 2008-03-10
Last month you helped me insert an image into a flexgrid where the text in a particular column read "Red", "yellow", or "Green".  My requirement has changed slightly and I now need to compare column 3 to column 8, column 4 to column 9, and column 5 to column 10.  I don't want Column 8, 9, or 10 to show in my display and I believe I did that by setting the columns to 8.  My problem is doing the compare.  The code you gave me as modified is:

FG1.Font.Name = "arial"
    FG1.Font.Size = "8"
    FG1.Font.Bold = True
    FG1.Cols = 8
    S = "          |<Code                 |<Program Title                                               |<Cost      |<Sched    |<Perf       |<Remarks                                                 |<Updated      "
    FG1.FormatString = S$
    FG1.WordWrap = True

    Dim nCnt1 As Long
    Dim nColNum As Long
    Dim sPicFile As String

    For nCnt1 = 1 To FG1.Rows - 1
        For nColNum = 3 To 5
            Select Case FG1.TextMatrix(nCnt1, nColNum)
       
            Case "Red"
                FG1.CellPictureAlignment = flexAlignCenterCenter
                sPicFile = "c:\205gui\YelRed.gif"
            Case "Green"
                FG1.CellPictureAlignment = flexAlignCenterCenter
                sPicFile = "c:\205gui\Grnred.gif"
            Case "Yellow"
                FG1.CellPictureAlignment = flexAlignCenterCenter
                sPicFile = "c:\205gui\Yelgrn.gif"
            Case Else
                sPicFile = ""
            End Select
            If sPicFile <> "" Then
                FG1.Col = nColNum
                FG1.Row = nCnt1
                Set FG1.CellPicture = LoadPicture(sPicFile)
                FG1.TextMatrix(nCnt1, nColNum) = ""
            End If
        Next nColNum
    Next nCnt1
    Call fixflex
End Sub

Private Sub fixflex()
    Dim nrow As Integer

    LblCellText.Width = FG1.ColWidth(6)

    For nrow = 1 To FG1.Rows - 1
        LblCellText.Caption = FG1.TextMatrix(nrow, 6)
        FG1.RowHeight(nrow) = LblCellText.Height + 240
    Next nrow
End Sub


any idea how I can compare the data in one field with the data in another to determine which gif to display???

Specifically, I now have 9 gifs for this grid.  They are grngrn, grn, yel, grnred, yelgrn, yelyel, yelred, redgrn, redyel, and redred.  Comparing the field coststat_1 to Coststat_2 tells me which gif to display.  I tried to insert an and statement in the code you gave me to look at the data in coststat_2 but got a datatype mismatch.

Any Ideas how to go about this???

Warren
0
Comment
Question by:clarwc
  • 4
  • 3
7 Comments
 
LVL 6

Expert Comment

by:clifABB
Comment Utility
I will take a look at this and see what's going on and get back to you.
0
 
LVL 6

Accepted Solution

by:
clifABB earned 50 total points
Comment Utility
OK, here are my ideas...
First of all, to hide a column, don't reset the columns to 8 as this deletes columns 8, 9, and 10.  What you need to do is set the column widths to 0:
  FG1.ColWidth(8) = 0
  FG1.ColWidth(9) = 0
  FG1.ColWidth(10) = 0

Second, I didn't see in your code, coststat_1 and Coststat_2.  I might assume, though, that these are strings ("Red", "Green" and "Yellow").  You cannot compare strings using AND.
Code I might use for this might be:
  sPic = ""
  Select Case coststat_1
    Case "Red"
      sPic = "red"
    Case "Green"
      sPic = "grn"
    Case "Yellow"
      sPic = "yel"
  End Select
  Select Case Coststat_2
    Case "Red"
      sPic = sPic & "red"
    Case "Green"
      sPic = sPic & "grn"
    Case "Yellow"
      sPic = sPic & "yel"
  End Select
  sPic = "c:\205gui\" & sPic & ".gif"
By the way, I'm sure you've heard this before, but hard coding paths is a no-no.

Finally, to expand on this and our earlier question, I would separate the color title columns from the actual gif columns.  That is to say, rather than using columns 3, 4, 5, 8, 9, and 10, with 3, 4, and 5 doing double duty and display, I would put the color captions in 8, 9, 10, 11, 12, and 13 (setting their widths to 0 as I noted above).  Doing this will allow for better debugging when needed.  With this in mind, you code to load the gifs would look something like this:
  For nCnt1 = 1 To FG1.Rows - 1
    For nColNum = 8 To 10
      sPicFile = ""
      Select Case FG1.TextMatrix(nCnt1, nColNum)
        Case "Red"
          sPicFile = "red"
        Case "Green"
          sPicFile = "grn"
        Case "Yellow"
          sPicFile = "yel"
        Case Else
          sPicFile = ""
      End Select
      Select Case FG1.TextMatrix(nCnt1, nColNum + 3)
        Case "Red"
          sPicFile = sPicFile & "red"
        Case "Green"
          sPicFile = sPicFile & "grn"
        Case "Yellow"
          sPicFile = sPicFile & "yel"
        Case Else
          sPicFile = sPicFile & ""
      End Select
      FG1.CellPictureAlignment = flexAlignCenterCenter
      sPicFile = "c:\205gui\" & sPicFile & ".gif"
      If sPicFile <> "" Then
        FG1.Col = nColNum
        FG1.Row = nCnt1
        Set FG1.CellPicture = LoadPicture(sPicFile)
        FG1.TextMatrix(nCnt1, nColNum) = ""
      End If
    Next nColNum
  Next nCnt1

I hope I understood your question and this answers it.

Actually, I'm on vacation this week and I just happened to log in from home to check what was up.
I'll continue to log in this week (in the evenings) to make sure you're set.

Good luck
0
 

Author Comment

by:clarwc
Comment Utility
In the code you originally helped me with all I needed to do was replace the text "Red" with the graphic for red and so forth.  Now I need to compare the contents of column 3 (Coststat_1) with Column 8 (Coststat_2), Column 4 (Schedstat_1) with Column 9 (Schedstat_2), and Column 5 (Perfstat_1) with Column 10 (Perfstat_2).  The results of the comparison will determine which of 12 graphics should be displayed in column 3, 4, and 5.  For example is Coststat_1 is "red" and Coststat_2 is yellow I need to display the graphic RedYel.gif in column 3.

Does that help?

Also, I didn't know better that to hard code the path.  how do I let the user decide where to save the HTML file?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:clifABB
Comment Utility
In my example above, the code I gave should be what you want.  I suggested placing the contents of columns 3, 4, and 5 in columns 11, 12, and 13 only as an aid in future debugging.  If you really need the contents of 3, 4, and 5, then replace the follwing lines of code:
Replace:
    For nColNum = 8 To 10
With:
    For nColNum = 3 To 5

Replace:
      Select Case FG1.TextMatrix(nCnt1, nColNum + 3)
With:
      Select Case FG1.TextMatrix(nCnt1, nColNum + 5)

To let the user select which path, you can use the common dialog control.  You can look up examples on how to use this control in the help file.
0
 

Author Comment

by:clarwc
Comment Utility
cliffab,

Thanks for the help.  I don't think I am making my problem clear.  I believe what I need will be some type of if else statement.  As an example for the first record something like:

If Data1.Recordset.Fields("coststat_1") = "Red" and Data1.Recordset.Fields("coststat_2") = "Yellow" Then

sPicFile = "c:\205gui\RedYel.gif"

else if

If Data1.Recordset.Fields("coststat_1") = "Red" and Data1.Recordset.Fields("coststat_2") = "Green" Then

sPicFile = "c:\205gui\Redgrn.gif"

Something like that but I don't know how to get it to co through each record and look at column 3 (which is coststat_1), then column 4 (schedstat_1), and column 5 (Perfstat_1)

Does this make better sense.

Warren

0
 
LVL 6

Expert Comment

by:clifABB
Comment Utility
I was under the impression that you wanted to go by the cell values, (which is what the field values are) rather than the field values themselves.  You stated in your original question above that you wanted to compare the values of column 3 with the values of column 8, etc.
It works the same either way.  I'm sorry but I suppose it was I who was not very clear.  If you have the column 3 value (or Data1.Recordset.Fields("coststat_1")) of "Red" and the column 8 value (or Data1.Recordset.Fields("coststat_2") value) of "Yellow"
then you can create a filename based on that ("Red" = "red" + "Yellow" = "yel").  This would be the most efficient way of doing what you ask.  However, if you want to use a compare type of code, I will give you that.
If/Then/Else statements are very ineffecient.  A Select/Case statement would be a much better idea.
  Select Case Data1.Recordset.Fields("coststat_1")
    Case "Red"
      Select Case Data1.Recordset.Fields("coststat_2")
        Case "Red"
          sPicFile = "redred.gif"
        Case "Yellow"
          sPicFile = "redyel.gif"
        Case "Green"
          sPicFile = "redgrn.gif"
        Case Else
          sPicFile = "red.gif"
      End Select
    Case "Yellow"
      Select Case Data1.Recordset.Fields("coststat_2")
        Case "Red"
          sPicFile = "yelred.gif"
        Case "Yellow"
          sPicFile = "yelyel.gif"
        Case "Green"
          sPicFile = "yelgrn.gif"
        Case Else
          sPicFile = "yellow.gif"
      End Select
    Case "Green"
      Select Case Data1.Recordset.Fields("coststat_2")
        Case "Red"
          sPicFile = "grnred.gif"
        Case "Yellow"
          sPicFile = "grnyel.gif"
        Case "Green"
          sPicFile = "grngrn.gif"
        Case Else
          sPicFile = "green.gif"
      End Select
  End Select
  sPicFile = "c:\205gui\" & sPicFile

On the other hand, an If/Then/Else statement might look like this:
  If Data1.Recordset.Fields("coststat_1") = "Red" Then
    If Data1.Recordset.Fields("coststat_2") = "Red" Then
      sPicFile = "redred.gif"
    ElseIf Data1.Recordset.Fields("coststat_2") = "Yellow" Then
      sPicFile = "redyel.gif"
    ElseIf Data1.Recordset.Fields("coststat_2") = "Green" Then
      sPicFile = "redgrn.gif"
    Else
      sPicFile = "red.gif"
    End If
  ElseIf Data1.Recordset.Fields("coststat_1") = "Yellow" Then
    If Data1.Recordset.Fields("coststat_2") = "Red" Then
      sPicFile = "yelred.gif"
    ElseIf Data1.Recordset.Fields("coststat_2") = "Yellow" Then
      sPicFile = "yelyel.gif"
    ElseIf Data1.Recordset.Fields("coststat_2") = "Green" Then
      sPicFile = "yelgrn.gif"
    Else
      sPicFile = "yellow.gif"
    End If
  ElseIf Data1.Recordset.Fields("coststat_1") = "Green" Then
    If Data1.Recordset.Fields("coststat_2") = "Red" Then
      sPicFile = "grnred.gif"
    ElseIf Data1.Recordset.Fields("coststat_2") = "Yellow" Then
      sPicFile = "grnyel.gif"
    ElseIf Data1.Recordset.Fields("coststat_2") = "Green" Then
      sPicFile = "grngrn.gif"
    Else
      sPicFile = "green.gif"
    End If
  End If
  sPicFile = "c:\205gui\" & sPicFile
0
 

Author Comment

by:clarwc
Comment Utility
cliffab,

That worked!!! Thanks a lot.


0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now