Select Case Possible?

[ryancgarrett]

Sorry I have so many posts, I'm just a noob with a big project to finish lol. So I'm pretty sure I can use Select Case for what I want to do, I'm just unsure of how to do it.

I have a form with a listbox and 5 option buttons. Each of the option buttons needs to populate the listbox with different values. On another sheet, I have the list of values to populate the listbox. These values are in column B, and in column A I have numbers next to each item, 100-600. Basically, if I click option 1 on my form I need all the values in column B that have the number 100-199 next to them in column A to go in the listbox. If option 2 is selected, 200-299 needs to go in the listbox etc.

How do I do this?!?!

 

[kpark91]

Hi, don't feel sorry for so many posts.
If you are having trouble with a problem, other people will too :P
So, ask as many as questions you'd like on mrexcel forum cuz it's a great help for me as well.

when you mean option buttons, do you mean command buttons? (just making sure)
In that case,
Let's say your commandbutton for option 1 name is "cmdbtn1."
and your listbox name is "lstbox."

Then your code would look like

Private Sub cmdbtn1_Click()
    Application.ScreenUpdating = False

    Dim LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row 'Get number of the last row
    
    For i = 1 To LR
        If Range("A" & i).Value >= 100 And Range("A" & i).Value < 200 Then
            lstbox.AddItem Range("B" & i).Value
        End If
    Next i

    Application.ScreenUpdating = True
End Sub

And if you want to clear all the items in the listbox afterward use code like

lstbox.Clear

 

[ryancgarrett]

By option buttons I mean there are 5 little bubbles, one of which can be filled in at a time. Sorry for my lack of technicality but I don't know of any other way to describe it. From what I understand they are different than command buttons. Is the code you gave still correct?

 

[kpark91]

Then. no it isn't.
Are you talking about the dropdown box??

 

[kpark91]

Ohhh I finally understand then.
Option button. I haven't used it before so my apologies xD

 

[kpark91]

Hello,
here is the revised code

If your option button name is "OptBtn1" and listbox name is "lstbox" then use

Private Sub OptBtn1_Change()
    Dim LR As Long, i As Long
    Application.ScreenUpdating = False
    If OptBtn1.Value = True Then
        
        LR = Range("A" & Rows.Count).End(xlUp).Row 'Get number of the last row
        
        For i = 1 To LR
            If Range("A" & i).Value >= 100 And Range("A" & i).Value < 200 Then
                lstbox.AddItem Range("B" & i).Value
            End If
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

You must include this whole code for each of your optionbutton and replacing the names of your option code and also replacing your range of values.
I cannot think of a way where you can just do it with 1 single event as I haven't dealt with them before

 

[ryancgarrett]

Thank you so much you saved my life!

 

[kpark91]

No problem
Thanks for the feedback.