VBA help needed

juster21

Well-known Member
Joined
Jun 3, 2005
Messages
867
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:
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!!!!!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Well-known Member
Joined
Jul 22, 2004
Messages
503
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):

Code:
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

Well-known Member
Joined
Jun 3, 2005
Messages
867
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

Well-known Member
Joined
Jun 3, 2005
Messages
867
Thanks SciFI....yeah, no orange here either so I changed that as well.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,931
Members
412,299
Latest member
agentless
Top