CODEWITHSUNDEEP
vbaexcel
Clarus Dignus
Clarus Dignus

Reputation: 3897

Excel VBA code for textbox value dependent on dropdown list value using named ranges

What I have:

  1. I have a custom user form in Excel VBA.
  2. The form contains a dropdown list (industry category) and textbox (corresponding industry specifier).
  3. For each industry category there is one industry specifier (an acronym version of the category).
  4. The industry category and industry specifier will always be of the same row.
  5. The dropdown list is populated from a name range of cells.

What I need:

The value of the textbox needs to be dependent on the value of the dropdown-list.

e.g. When an industry category is selected, the corresponding industry code should appear in the textbox.

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 Specifier):

ag
ap
at
cn
co
ea

My VBA code:

Populating the dropdown list for industry 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

Textbox for industry specifier:

IndustrySpecifier.Value = ""

What I've tried:

I've reviewed tutorials on how to achieve what I need using VBA code alone but I don't know where to begin using dependent named ranges

Upvotes: 4

Views: 10584

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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

enter image description here

Upvotes: 4

Related Questions