To Select Case or Not to Select Case

[joeyjj]

Hello All,

I am making a VBA program that when run a dialogue box with a combo box appears which gives you a list of options to pick. From that list if you select option 1 a new combo box appears and with several other options to choose from. Each of the options from combo box 1 has a respective new combo box 2 that appears when it is selected. I have figured this part out by myself, however the next part is my biggest issue.

I am trying to design a code that will look for the answer of combo box 2 and depending on which selection is chosen from combo box 2 output a result.

For Example: If combo box 1's options are Red Yellow Green then Red would have a new combo box that appears with the following options Dark Red, Light Red, Pink. If Yellow is selected a Different combo box appears which shows the following Dark Yellow, Light Yellow, White. And the same kind of idea for green.

What I would like to do is take find the answer to any of the combo box 2s (remember they are all different combo boxes with different names) options and display the result.


Any Help is much appreciated!

 

[Nogslaw]

Depends on how you have your boxes set up. The ideal solution is to update your code so that there are only 2 Comboboxes. The values in the second box are dependent upon the selection made in the first one. There are lots of posts on here about how to do that. Than, you can just base your Select off of the 2nd Combobox, because all of the values are different.


But from your description, it sounds like you have different Comboboxes display as a result of your 1st one. It isn't ideal, but in that case, I'd use two Select Case sections:

Select Case True
    Case Combobox1.Value = "Red"
        Select Case True
            Case Combobox2.Value = "Light Red"
                'Your Code for this selection
            Case Combobox2.Value = "Dark Red"
                'Your Code for this selection
        End Select
    Case Combobox1.Value = "Yellow"
        Select Case True
            Case Combobox3.Value = "Light Yellow"
                'Your Code for this selection
            Case Combobox3.Value = "Dark Yellow"
                'Your Code for this selection
        End Select
End Select

 

[Norie]

Are you storing the data anywhere?

If you aren't it might be an idea to do that, it could make this process a lot easier.

One really basic way would be to have a named range for each of the options available in the first combobox.

The named ranges would simply be a list of the items you want to display in the second combobox.

So for the named range 'Red' you would have a list of Dark Red, Light Red, Pink.

So to populate the 2nd combobox you just use the named range associated with the selected item for the rowsource.

The code would look something like this

Private Sub Combobox1_Change()
 
       If Combobobox1.ListIndex <> -1 Then
 
              Combobox2.List = Range(Combobobox1.Value).Value
       End if
End Sub

Actually I think I totally misinterpreted this, do you have already have individual comboboxes for each of the items in the first combobox?