Multiple dependent combo boxes in a User form

[bromers]

Greetings everyone,

Upon coming across this very helpful forum, I came across a post that up-until now has been extremely helpful. The link to the thread can be found here.

The project that I have been given at work was to create a user form that has dependent combo boxes that will be used to check the prices on our Export distribution couriers.

The userform that i have created has 5 combo boxes and one label (where the price will be displayed). The combo boxes that I have on the form and which they are dependent on are as follows:

1. cmb_courier
2. cmb_country
3. cmb_region
4. cmb_ptype
5. cmb_qty

The combo boxes are dependent as follows:

cmb_country is dependent on what courier has been selected
cmb_region is dependent on what courier and country has been selected
cmb_ptype is dependent on what courier, country and region has been selected
cmb_qty is dependent on what courier, country, region and pallet type has been selected

So far, I have been able to get the first four combo boxes dependent as shown above. Where I am having my issue is on the final combo box cmb_qty. I can get each price to be outputted onto the label on the userform. However, when i try and select the second pallet type, then select the pallet quantity, no price is outputted.

Below is the section of code for where I am having this problem.

Private Sub cmb_qty_Change()
'cmb_qty = "" lbl_cost = hidden
    If cmb_qty = "" Then
        lbl_cost.Visible = False       'Hidden
    End If
    
    'if cmb_courier = Hexagon, cmb_country = France, cmb_region = All Regions & cmb_ptype = Euro then show list
    If cmb_courier = "Hexagon Freight Ltd" Then
        If cmb_country = "France" Then
            If cmb_region = "All Region" Then
                If cmb_ptype = "Euro Pallet" Then
                    
                   If cmb_qty = "1" Then
                        lbl_cost.Visible = True
                        lbl_cost = "£135"
                        
                ElseIf cmb_qty = "2" Then
                        lbl_cost.Visible = True
                        lbl_cost = "£165"
                ElseIf cmb_qty = "3" Then
                        lbl_cost.Visible = True
                        lbl_cost = "£200"
                ElseIf cmb_qty = "4" Then
                        lbl_cost.Visible = True
                        lbl_cost = "£225"
                ElseIf cmb_qty = "5" Then
                        lbl_cost.Visible = True
                        lbl_cost = "£250"
                         
           Else
                If cmb_ptype = "Standard Pallet" Then
                
                    If cmb_qty = "1" Then
                        lbl_cost.Visible = True
                        lbl_cost = "£145"
                        
                Else: cmb_qty = "2"
                        lbl_cost.Visible = True
                        lbl_cost = "£175"
                End If
                End If
                End If
                End If
                End If
                End If
                End If
                 
End Sub

I can fully understand that its a large section of code, I can upload the file with all the code if the full code is needed to be seen working.

I thank you all in anticipation for any and all help you can give.

Kind Regards
Scott

 

[bromers]

Update!

I have managed to find a way around my problem.

Instead of using the IF ElseIf statements I decided to try it with using the Select Case statement which has worked. Below is the code that I used to fix the problem.

Private Sub cmb_qty_Change()
'cmb_qty = "" lbl_cost = hidden
    If cmb_qty = "" Then
        lbl_cost.Visible = False       'Hidden
    End If
    
    'if cmb_courier = Hexagon, cmb_country = France, cmb_region = All Regions & cmb_ptype = Euro
    If cmb_courier = "Hexagon Freight Ltd" Then
        If cmb_country = "France" Then
            If cmb_region = "All Regions" Then
                Select Case cmb_ptype
                    Case "Euro Pallet"
                        Select Case cmb_qty
                            Case Is = "1"
                                lbl_cost.Visible = True
                                lbl_cost = "£135"
                            Case Is = "2"
                                lbl_cost.Visible = True
                                lbl_cost = "£165"
                            Case Is = "3"
                                lbl_cost.Visible = True
                                lbl_cost = "£200"
                            Case Is = "4"
                                lbl_cost.Visible = True
                                lbl_cost = "£225"
                            Case Is = "5"
                                lbl_cost.Visible = True
                                lbl_cost = "£250"
                            Case Is = "6"
                                lbl_cost.Visible = True
                                lbl_cost = "£275"
                        End Select
                End Select
                
                Select Case cmb_ptype
                    Case "Standard Pallet"
                        Select Case cmb_qty
                            Case Is = "1"
                                lbl_cost.Visible = True
                                lbl_cost = "£165"
                            Case Is = "2"
                                lbl_cost.Visible = True
                                lbl_cost = "£200"
                            Case Is = "3"
                                lbl_cost.Visible = True
                                lbl_cost = "£225"
                            Case Is = "4"
                                lbl_cost.Visible = True
                                lbl_cost = "£250"
                            Case Is = "5"
                                lbl_cost.Visible = True
                                lbl_cost = "£275"
                            Case Is = "6"
                                lbl_cost.Visible = True
                                lbl_cost = "£300"
                        End Select
                End Select
            End If
        End If
    End If

Hope that this maybe useful to others.

Regards
Scott