VBA help needed

[juster21]

I am trying to use a "Select...Case" statement but am missing something.
There is a combo box that the user will choose either Medium, High, or Urgent. Below is my code:

Private Sub cboPriorityLevel_Change()
    Select Case True
        Case "Urgent"
            If cboPriorityLevel.Value = "Urgent" Then
            cboPriorityLevel.BackColor = vbRed
        Case "High"
            If cboPriorityLevel.Value = "High" Then
            cboPriorityLevel.BackColor = vbOrange
        Case "Medium"
            If cboPriorityLevel.Value = "Medium" Then
            cboPriorityLevel.BackColor = vbBlue
    End Select
End Sub

I get a "Case without Select Case" compile error. What am I doing wrong?
Thanks!!!!!

 

[Smitty]

Not tested, but:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cboPriorityLevel_Change()
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> cboPriorityLevel.Text
        <SPAN style="color:#00007F">Case</SPAN> "Urgent"
            cboPriorityLevel.BackColor = vbRed
        <SPAN style="color:#00007F">Case</SPAN> "High"
            cboPriorityLevel.BackColor = vbOrange
        <SPAN style="color:#00007F">Case</SPAN> "Medium"
            cboPriorityLevel.BackColor = vbBlue
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

In the Select Case statement, you're predefining the "case" that you're checking, so that's where the cboPriorityLevel should be, as that's what's being evaluated. As opposed to trying to integrate an IF statement.

HTH,

Smitty

 

[Von Pookie]

You don't have an "End If" for any of the 3 If statments. Need to have those if you don't have the entire statement on one line--one "End If" per "If...then"

 

[scifibum]

So, no "If" statements are needed, if you have them you have to use "End If" statements unless your "Then" action is on the same line as the If statement, your "select" comparison was checking the value of "True" which is always "true", and finally, there is no "vbOrange" constant (at least on my machine). So this should work (i tested):

Private Sub cboPriorityLevel_Change()
    Select Case cboPriorityLevel.Value
        Case "Urgent"
            cboPriorityLevel.BackColor = vbRed
        Case "High"
            cboPriorityLevel.BackColor = RGB(255, 150, 0)
        Case "Medium"
            cboPriorityLevel.BackColor = vbBlue
    End Select
End Sub

 

[juster21]

Smitty,
That's what I was missing. I think I was thinking I would keep throwing code at it and that would fix it. Select statements have always caused me fits but this helped alot. Thanks!!

 

[juster21]

Thanks SciFI....yeah, no orange here either so I changed that as well.