dependent Combo box and error handling

[Arie Bos]

Hello Excel Masters,

I have 1st combobox with ' MaterialType' This reads from a range called 'NomRange1' Each item in NomRange1 has a named sub range 'NomRange2' so the second combobox 'MaterialSelect' reads from these ranges. Some of the items in the subrange have again their own named sub range, which populates the third combobox 'MaterialSelect2'. The code below does this alright, except for these items that do not have a second level sub range. The code halts and gives a 1004 error, which is logical.
Is it possible to build in an error handler so the code will accept if there is no second level sub range?

Private Sub ComboMaterialType_Change()
If ComboMaterialType.Value = "" Then Exit Sub
Dim NomRange1 As String
NomRange1 = ComboMaterialType.Text
    ComboMaterialSelect.List = Application.Transpose(Range(NomRange1))
End Sub

Private Sub ComboMaterialSelect_Change()
If ComboMaterialSelect.Value = "" Then Exit Sub
Dim NomRange2 As String
NomRange2 = ComboMaterialSelect.Text
    ComboMaterialSelect2.List = Application.Transpose(Range(NomRange2))
End Sub

 

[Fluff]

How about

   Dim Rng As Range
   
   If Me.ComboMaterialSelect.Value = "" Then Exit Sub
   On Error Resume Next
   Set Rng = Range(Me.ComboMaterialSelect.Text)
   On Error GoTo 0
   If Not Rng Is Nothing Then
      Me.ComboMaterialSelect2.List = Rng.Value
   Else
      Me.ComboMaterialSelect2.Clear
   End If

 

[Arie Bos]

Hello Again Fluff!
And thank you for the quick answer.
I replaced my full code above with yours. The 'MaterialSelect' box does not yet get filled however... see image.

 

[Fluff]

That code isn't filling the MaterialSelect combo, but the MaterialSelect2 combo.

 

[Arie Bos]

That code isn't filling the MaterialSelect combo, but the MaterialSelect2 combo.

Got it... I added it to my existing block and it works perfectly. Thanks again Fluff
Arie

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Arie Bos]

Hi again Fluff,

I added your code and it worked good, but suddenly I get an error message at the very last end sub stating 'Block if without end if' as soon as I select something in ComboMaterialSelect.
Also, the label for the ComboMaterialSelect2 combobox does not update itself. I checked above, and copied your code in again, but the hick-up seems to somewhere else.

As always, your help is greatly appreciated.
A

'MATERIAL SELECTION
'change label captions before combobox "ComboMaterialSelect".
Private Sub ComboMaterialType_click()
 ComboMaterialType.List = Application.Transpose(Range("MaterialTypeRange"))
    If ComboMaterialType.Text <> "" Then
       ComboMaterialSelect.Visible = True
       lblMaterialSelect.Visible = True
    ElseIf ComboMaterialType.Text = "Food_Vegan" Then
        lblMaterialSelect.Caption = "Select type of food"
    ElseIf ComboMaterialType.Text = "Animal" Then
        lblMaterialSelect.Caption = "Select Animal"
    ElseIf ComboMaterialType.Text = "Fibers" Then
        lblMaterialSelect.Caption = "Select type of fibre"
    ElseIf ComboMaterialType.Text = "Stone_Glass" Then
        lblMaterialSelect.Caption = "Select Type of Stone"
    ElseIf ComboMaterialType.Text = "Synthetic" Then
        lblMaterialSelect.Caption = "Select Synthetic"
    ElseIf ComboMaterialType.Text = "Waste" Then
        lblMaterialSelect.Caption = "Select type of Waste"
    ElseIf ComboMaterialType.Text = "Other" Then
        lblMaterialSelect.Caption = "Select Other Material"
    End If
End Sub

'Combobox selection process
Private Sub ComboMaterialType_Change()
If ComboMaterialType.Value = "" Then Exit Sub
Dim NomRange As String
NomRange = ComboMaterialType.Text
    ComboMaterialSelect.List = Application.Transpose(Range(NomRange))
End Sub

Private Sub ComboMaterialSelect_Change()
'change label captions before combobox "ComboMaterialSelect2".
    If ComboMaterialSelect.Text <> "" Then
        ComboMaterialSelect2.Visible = True
        lblMaterialSelect2.Visible = True

   If ComboMaterialSelect.Text = "Fruits" Then
        lblMaterialSelect2.Caption = "Which Fruit?"
      ElseIf ComboMaterialSelect.Text = "Vegetables" Then
        lblMaterialSelect2.Caption = "Which Vegetable?"
      ElseIf ComboMaterialSelect.Text = "Beans" Then
        lblMaterialSelect2.Caption = "What Beans?"
      ElseIf ComboMaterialSelect.Text = "Grains" Then
        lblMaterialSelect2.Caption = "What Grain?"
      ElseIf ComboMaterialSelect.Text = "Herbs" Then
        lblMaterialSelect2.Caption = "Which Herbs?"
      ElseIf ComboMaterialSelect.Text = "Seafood" Then
        lblMaterialSelect2.Caption = "What Seafood?"
      ElseIf ComboMaterialSelect.Text = "Nuts" Then
        lblMaterialSelect2.Caption = "What Nut?"
      ElseIf ComboMaterialSelect.Text = "Other" Then
        lblMaterialSelect2.Caption = ""
   End If

   Dim Rng As Range
   
   If Me.ComboMaterialSelect.Value = "" Then Exit Sub
   On Error Resume Next
   Set Rng = Range(Me.ComboMaterialSelect.Text)
   On Error GoTo 0
   If Not Rng Is Nothing Then
      Me.ComboMaterialSelect2.List = Rng.Value
   Else
      Me.ComboMaterialSelect2.Clear
   End If
'END MATERIAL SELECTION
End Sub

 

[Fluff]

You're missing an End If from the 1st If statement.