Case Statement working intermittently

[thabart]

I have written a case statement in VBA to color code a report. The report is to show expiring contracts. Unfortunately, the CASE statement is not working right, it colors the first, third, and last correctly but not the second.

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)


Select Case Me.Line_of_Business.Text

Case ""

Case "Commercial"
Me.GroupHeader0.BackColor = RGB(0, 0, 255)
Case "Government"
Me.GroupHeader0.BackColor = RGB(0, 255, 0)
Case "Infrastructure"
Me.GroupHeader0.BackColor = RGB(255, 0, 0)
Case Else
Me.GroupHeader0.BackColor = RGB(255, 255, 51)
Me.Line_of_Business.ForeColor = RGB(0, 0, 0)
Me.Label29.ForeColor = RGB(0, 0, 0)
Me.Label30.ForeColor = RGB(0, 0, 0)
Me.Label31.ForeColor = RGB(0, 0, 0)
Me.Label32.ForeColor = RGB(0, 0, 0)
Me.Label33.ForeColor = RGB(0, 0, 0)

End Select


End Sub

Any help would be appreciated.

Tim

 

[thabart]

Update:

I just ran the report again and the second CASE worked but the third did not.

I changed the code to change the font to work with the new colors

Select Case Me.Line_of_Business.Text

Case ""

Case "Commercial"
Me.GroupHeader0.BackColor = RGB(0, 0, 255)
Me.Line_of_Business.ForeColor = RGB(255, 255, 255)
Me.Label29.ForeColor = RGB(255, 255, 255)
Me.Label30.ForeColor = RGB(255, 255, 255)
Me.Label31.ForeColor = RGB(255, 255, 255)
Me.Label32.ForeColor = RGB(255, 255, 255)
Me.Label33.ForeColor = RGB(255, 255, 255)
Case "Government"
Me.GroupHeader0.BackColor = RGB(0, 255, 0)
Me.Line_of_Business.ForeColor = RGB(0, 0, 0)
Me.Label29.ForeColor = RGB(0, 0, 0)
Me.Label30.ForeColor = RGB(0, 0, 0)
Me.Label31.ForeColor = RGB(0, 0, 0)
Me.Label32.ForeColor = RGB(0, 0, 0)
Me.Label33.ForeColor = RGB(0, 0, 0)
Case "Infrastructure"
Me.GroupHeader0.BackColor = RGB(255, 0, 0)
Case Else
Me.GroupHeader0.BackColor = RGB(255, 255, 51)
Me.Line_of_Business.ForeColor = RGB(0, 0, 0)
Me.Label29.ForeColor = RGB(0, 0, 0)
Me.Label30.ForeColor = RGB(0, 0, 0)
Me.Label31.ForeColor = RGB(0, 0, 0)
Me.Label32.ForeColor = RGB(0, 0, 0)
Me.Label33.ForeColor = RGB(0, 0, 0)

End Select


Ran it again and the fonts worked but not the background colors for the second one again.

Tim

 

[Minty]

Why are you using the Text property and not the value, that could give you some strange results?

Case "" is an interesting one, why isn't that handled by a check on valid data or in the case else?

 

[thabart]

Why are you using the Text property and not the value, that could give you some strange results?

Case "" is an interesting one, why isn't that handled by a check on valid data or in the case else?

I changed it to value instead of text and removed the Case ""

Ran it again and got the same results

Tim

 

[Minty]

Okay so now we need to "see" what your data is evaluating to rather than what you think it is evaluating to. Add this one line in;

    Select Case Me.Line_of_Business
           
        Debug.Print "My Value is :" & Me.Line_of_Business & " !"
        Case "Commercial"
            Me.GroupHeader0.BackColor = RGB(0, 0, 255)
            Me.Line_of_Business.ForeColor = RGB(255, 255, 255)
            Me.Label29.ForeColor = RGB(255, 255, 255)
            Me.Label30.ForeColor = RGB(255, 255, 255)
            Me.Label31.ForeColor = RGB(255, 255, 255)
            Me.Label32.ForeColor = RGB(255, 255, 255)
            Me.Label33.ForeColor = RGB(255, 255, 255)
        Case "Government"
            Me.GroupHeader0.BackColor = RGB(0, 255, 0)
            Me.Line_of_Business.ForeColor = RGB(0, 0, 0)
            Me.Label29.ForeColor = RGB(0, 0, 0)
            Me.Label30.ForeColor = RGB(0, 0, 0)
            Me.Label31.ForeColor = RGB(0, 0, 0)
            Me.Label32.ForeColor = RGB(0, 0, 0)
            Me.Label33.ForeColor = RGB(0, 0, 0)
        Case "Infrastructure"
            Me.GroupHeader0.BackColor = RGB(255, 0, 0)
        Case Else
            Me.GroupHeader0.BackColor = RGB(255, 255, 51)
            Me.Line_of_Business.ForeColor = RGB(0, 0, 0)
            Me.Label29.ForeColor = RGB(0, 0, 0)
            Me.Label30.ForeColor = RGB(0, 0, 0)
            Me.Label31.ForeColor = RGB(0, 0, 0)
            Me.Label32.ForeColor = RGB(0, 0, 0)
            Me.Label33.ForeColor = RGB(0, 0, 0)

    End Select

And have a look in the immediate window -(Pres CTRL & G in the vba editor to bring it up)

 

[Gasman]

Perhaps Trim() Line_of_Business as well?

 

[thabart]

I added that line and got an error of "Compile Error, Statements and labels invalid between Select Case and first Case"

 

[Gasman]

I added that line and got an error of "Compile Error, Statements and labels invalid between Select Case and first Case"

Well you would clean the data with Trim() BEFORE you test the case logic.

That is the whole idea of the Trim(). Perhaps display the LEN() of Line_Of_Business as well?, anything to identify exactly what it contains?

 

[thabart]

I added it after each CASE statment and got

My Value is :Commercial !
My Value is :Commercial !
My Value is :Government !
My Value is :Government !
My Value is :Government !
My Value is :Government !
My Value is :Infrastructure !
My Value is :Infrastructure !
My Value is :Commercial !
My Value is :Commercial !
My Value is :Government !



But the color is not changing for the Government one

 

[Deleted member 147267]

Ran it again and the fonts worked but not the background colors for the second one again.

Maybe state exactly what you expect to happen. If a Select Case block encounters a True condition, the selection is over and done. Are you expecting each case to be evaluated regardless?

 

[Isaac]

Possibly, your form might benefit from a Me.Repaint command. I'm not sure in this case.

 

[thabart]

It is a report, and it should, where the line of business is Commercial set the back color to blue, when Government set the back color to Green, and when Infrastructure Red.

 

[Isaac]

It is a report,

Ahh.

What event do you have this in? It may be a matter of which event. You might try using conditional formatting.

 

[thabart]

I have it in the "On Load" event. I was not able to figure out conditional formatting for the header of the report.

 

[Isaac]

I think you might need it in the header section's Format event.

 

[thabart]

I moved it there and got the same result.

 

[Isaac]

Hmm. not sure what else to suggest, other than stepping through the code line by line while it's running to make sure what you think is happening is happening. And uploading a sanitized version of the database for us to check out.

 

[thabart]

Well, thank you for your efforts

Tim

 

[Deleted member 147267]

As i pointed out in post 10 - a select case block will not do what you described in post 12...
EDIT - I had to run - meat thermometer was beeping at me.
What I mean is that if you have 2 or more such conditions, a Select Case block won't work for more than one. I'm not sure you ever clarified if that was what you expect or not.

 

[thabart]

As i pointed out in post 10 - a select case block will not do what you described in post 12...
EDIT - I had to run - meat thermometer was beeping at me.
What I mean is that if you have 2 or more such conditions, a Select Case block won't work for more than one. I'm not sure you ever clarified if that was what you expect or not.

My expectation is that the select case block will look compare each group to the first case and, if it is true then change the header background to the appropriate color, then check it against the second group and if it is true change the background color to green, then the third one and change the color appropriately.

Your response that it will not work for more than one does not make sense to me as it works on the first, third, and fourth, but not the second. And it doesn't matter if I move the second one up or down in the list. It doesn't like the comparison for that particular item.

Tim