文本框值的 Excel VBA 代码取决于使用命名范围的下拉列表值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22696111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA code for textbox value dependent on dropdown list value using named ranges
提问by Clarus Dignus
What I have:
我拥有的：
- I have a custom user form in Excel VBA.
- The form contains a dropdown list (industry category) and textbox (corresponding industry specifier).
- For each industry categorythere is one industry specifier(an acronym version of the category).
- The industry categoryand industry specifierwill always be of the same row.
- The dropdown list is populated from a name range of cells.
- 我在 Excel VBA 中有一个自定义用户表单。
- 该表单包含一个下拉列表（行业类别）和文本框（相应的行业说明符）。
- 对于每个行业类别，都有一个行业说明符（该类别的首字母缩写词）。
- 在产业类别和行业符永远是同一行的。
- 下拉列表由单元格的名称范围填充。
What I need:
我需要的：
The value of the textbox needs to be dependent on the value of the dropdown-list.
文本框的值需要依赖于下拉列表的值。
e.g. When an industry categoryis selected, the corresponding industry codeshould appear in the textbox.
eg When an industry categoryis selected, the corresponding industry codeshould appear in the textbox.
My cell structure:
我的细胞结构：
Column A (Industry Category):
A栏（行业类别）：
Agriculture
Art and photography
Arts and theatre
Charity and non-profit
Corporate
Educational and academic
Column B (Industry Specifier):
B 栏（行业说明符）：
ag
ap
at
cn
co
ea
My VBA code:
我的 VBA 代码：
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
我已经查看了有关如何单独使用 VBA 代码实现所需内容的教程，但我不知道从哪里开始使用相关命名范围
采纳答案by Dmitry Pavliv
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
