Select Case Question...

[BrianExcel]

For some reason I cannot get this code to work. I have 5 options in a combo box that a user can select. let's call them 1, 2, 3, 4 and 5.

The point of my code is to populate a listbox with the appropriate values depending on what's selected in the combo box. If they select 1, populate the range r1. If they select "2" then populate range r2 and so on.

The following code is what I'm trying, but when I run the code (set to the CHANGE event on the combo box) it gives the error: "Compile error: Statements and labels invalid between Select Case and first case."

Can anyone help?

Private Sub cmbSize_Change()
      
    Select Case 1
        UserForm1.lbxStock.RowSource = r1
    Case 2
        UserForm1.lbxStock.RowSource = r2
    Case 3
        UserForm1.lbxStock.RowSource = r3
    Case 4
        UserForm1.lbxStock.RowSource = r4
    Case 5
        UserForm1.lbxStock.RowSource = r5
    End Select
End Sub

 

[MrKowz]

You need to define the Select Case block (along with what value it needs to look at) before you can define individual cases:

Private Sub cmbSize_Change()
 
    Select Case combobox1.Value
        Case 1
            UserForm1.lbxStock.RowSource = r1
        Case 2
            UserForm1.lbxStock.RowSource = r2
        Case 3
            UserForm1.lbxStock.RowSource = r3
        Case 4
            UserForm1.lbxStock.RowSource = r4
        Case 5
            UserForm1.lbxStock.RowSource = r5
    End Select
End Sub

 

[VoG]

Try like this

Private Sub cmbSize_Change()
Select Case cmbSize_Change.Value
    Case 1: UserForm1.lbxStock.RowSource = r1
    Case 2: UserForm1.lbxStock.RowSource = r2
    Case 3: UserForm1.lbxStock.RowSource = r3
    Case 4: UserForm1.lbxStock.RowSource = r4
    Case 5: UserForm1.lbxStock.RowSource = r5
End Select
End Sub

 

[BrianExcel]

This sort of worked, but let me explain a little further...

Inside the combo box I have sizes of paper (Letter, Legal, Tabloid, Super and Custom).

I have ranges that have been pre-named on another sheet the same way. "Letter" range contains letter papers, "Legal" contains legal, etc.

When I select a value from the combo box previously mentioned, I want to update a corresponding listbox (lbxStock) with the range that corresponds with that value.

What you guys gave me worked in the sense that it no longer gave errors, but the listbox didn't update with the correct range either. It's like the code did nothing.

Any additional thoguhts? And thanks for the help!

 

[VoG]

Maybe like this

Case 1: UserForm1.lbxStock.RowSource = Range("r1").Address

 

[BrianExcel]

Still not working. It's like it's not calling the range or not updating the listbox. The listbox just stays blank...

 

[VoG]

Maybe

Case 1: UserForm1.lbxStock.RowSource = Range("r1").Address(external:=True)

 

[BrianExcel]

It took some working, but I finally got it with this...

Sub cmbSize_Change()

With UserForm1
    If cmbSize.Value = "Letter" Then
        .lbxStock.RowSource = "Letter"
    End If
    If cmbSize.Value = "Legal" Then
        .lbxStock.RowSource = "Legal"
    End If
    If cmbSize.Value = "Tabloid" Then
        .lbxStock.RowSource = "Tabloid"
    End If
    If cmbSize.Value = "Super" Then
        .lbxStock.RowSource = "Super"
    End If
    If cmbSize.Value = "Custom" Then
        .lbxStock.RowSource = "Custom"
    End If

End With
End Sub

 

[Norie]

Why not just use the value selected for the source of the listbox?

They appear to correspond to the named ranges.

If cmbSize.ListIndex<>-1 Then
       lbxStock.RowSource = cmbSize.Value
End If