Excel VBA code for a textbox value based on a drop-down list using named ranges

Question

What I have:

• I have a custom form in Excel VBA.
• The form contains a drop-down list ( industry category ) and a text field (corresponding industry specifier ).
• For each category there is one industry specifier (version of category abbreviation).
• the industry category and industry qualifier will always have the same line.
• The drop-down list is populated from the range of cell names.

What I need:

The value of the text field should depend on the value of the drop-down list.

eg. If an industry category is selected , the appropriate industry code should appear in the text box .

My cell structure:

Column A ( Industry Category ):

Agriculture                               
Art and photography                       
Arts and theatre                          
Charity and non-profit                    
Corporate                                   
Educational and academic                

Column B ( Industry Index ):

ag
ap
at
cn
co
ea

My VBA code is:

Populating a drop-down list for a category category :

'Populate Industry combo box.
Dim range_c As Range
Dim ws_c As Worksheet
Set ws_c = Worksheets("4.1 List data")

For Each range_c In ws_c.Range("IndustryList")
  With Me.Industry
    .AddItem range_c.Value
    .List(.ListCount - 1, 1) = range_c.Offset(0, 1).Value
  End With
Next range_c

Text field for industry qualifier :

IndustrySpecifier.Value = ""

What I tried:

I looked at tutorials on how to achieve what I need using VBA code, but I don't know where to start using named ranges.

Answer 1

You need something like this:

Private Sub UserForm_Initialize()
    Dim range_c As Range

    For Each range_c In Worksheets("4.1 List data").Range("IndustryList")
      With Me.Industry
        .AddItem range_c.Value
        .List(.ListCount - 1, 1) = range_c.Offset(0, 1).Value
      End With
    Next range_c
End Sub

Private Sub Industry_Change()
    With Me.Industry
        If .ListIndex = -1 Then
            IndustrySpecifier.Text = ""
        Else
            IndustrySpecifier.Text = .List(.ListIndex, 1)
        End If
    End With
End Sub