Solved

Flexgrid question for cliffab

Posted on 1998-07-20
7
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 6

Expert Comment

by:clifABB
ID: 1466243
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
ID: 1466244
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
ID: 1466245
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 6

Expert Comment

by:clifABB
ID: 1466246
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
ID: 1466247
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
ID: 1466248
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
ID: 1466249
cliffab,

That worked!!! Thanks a lot.


0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…

696 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