文本框值的Excel VBA代码取决于使用命名范围的下拉列表值 [英] Excel VBA code for textbox value dependent on dropdown list value using named ranges

问题描述

1. 我在Excel VBA中有一个自定义用户表单.
2. 该表单包含一个下拉列表( 行业类别 )和文本框(对应的 行业说明符 ).
3. /li>
4. 对于每个 行业类别 ，都有一个 行业说明符 (该类别的缩写).
5. 行业类别 和 行业说明符 将始终位于同一行.
6. 从单元格的名称范围填充下拉列表.

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.

A列( 行业类别 ):

Column A (Industry Category):

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

B列( 行业说明 ):

Column B (Industry Specifier):

ag
ap
at
cn
co
ea

我的VBA代码:

填充 行业类别 的下拉列表:

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 = ""

我尝试过的事情:

我已经阅读了有关如何仅使用VBA代码来实现所需功能的教程，但我不知道从哪里开始使用相关的命名范围

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

推荐答案

您需要这样的内容:

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

这篇关于文本框值的Excel VBA代码取决于使用命名范围的下拉列表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！