vba 如何按名称获取控件名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12333789/
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
How to get a name of control by name?
提问by Damon Aw
I have a simple function where there's a combo box. If combo box's value is equal to "Disable", I'll disable textbox B. There are many combo boxes with their corresponding textbox B, arranged in rows and named by hand. If combo box A is named Product1, textbox B will be named Product1_status
我有一个简单的功能，其中有一个组合框。如果组合框的值等于“禁用”，我将禁用文本框 B。有许多组合框及其对应的文本框 B，按行排列并手动命名。如果组合框 A 被命名Product1，则文本框 B 将被命名Product1_status
I was thinking something like:
我在想这样的事情：
If value_of_a = "disable" Then
Dim name_of_b as String
name_of_b = Me.Combo.Name + "_status"
get_object_by_name(name_of_b).Enabled = False
End If
How do I do this?
我该怎么做呢？
采纳答案by Doug Glancy
I'm not sure how you are calling this, but here's a self-contained procedure that should help:
我不确定你是如何称呼它的，但这里有一个独立的程序，应该会有所帮助：
Sub test()
Dim ws As Excel.Worksheet
Dim ProductCombo As OLEObject
Dim ProductText As OLEObject
Set ws = ThisWorkbook.Sheets(1)
With ws
Set ProductCombo = .OLEObjects("Product1")
Set ProductText = .OLEObjects(ProductCombo.Name & "_status")
ProductText.Enabled = ProductCombo.Object.Text <> "Disabled"
End With
End Sub
EDIT: I really hate worksheet controls - I start from scratch every time I program them! Nonetheless, I thought I'd add this subroutine which resets every textbox whose name fits the patter Product#_status, according to its paired combobox. The logic does assume the names start with Product1, Product2, etc., without a gap in the numbering:
编辑：我真的很讨厌工作表控件 - 每次编程时我都从头开始！尽管如此，我想我会添加这个子程序，它会根据其配对的组合框重置名称符合模式 Product#_status 的每个文本框。该逻辑确实假定名称以 Product1、Product2 等开头，编号中没有间隙：
Sub test2()
Dim ws As Excel.Worksheet
Dim ctl As OLEObject
Dim i As Long
Dim ProductComboboxesCount
Dim ProductCombo As OLEObject
Dim ProductText As OLEObject
Const ControlPrefix As String = "Product"
Set ws = ThisWorkbook.Sheets(1)
With ws
For Each ctl In .OLEObjects
If TypeOf ctl.Object Is MSForms.ComboBox And Left(ctl.Name, Len(ControlPrefix)) = ControlPrefix Then
ProductComboboxesCount = ProductComboboxesCount + 1
End If
Next ctl
For i = 1 To ProductComboboxesCount
Set ProductCombo = .OLEObjects(ControlPrefix & i)
Set ProductText = .OLEObjects(ControlPrefix & i & "_status")
ProductText.Enabled = ProductCombo.Object.Text <> "Disabled"
Next i
End With
End Sub
回答by Nate-Wilkins
VBA
VBA
Edit: (Change for an actual VBA macro)
编辑：（更改为实际的 VBA 宏）
Sub Macro1()
'
' GetControl By Name
'
If value_of_a = "disable" Then
GetControl(ComboBox1.Name + "_status").Enabled = False
End If
End Sub
Function GetControl(nameOfControl As String) As OLEObject
Dim ctrl As OLEObject
For Each ctrl In ActiveSheet.OLEObjects
If ctrl.Name = nameOfControl Then
Set GetControl = ctrl
End If
Next ctrl
End Function
VB.Net
VB.Net
Code for VB.Net if anyone wants it for that reason:
VB.Net 的代码，如果有人因为这个原因想要它：
Sub Main()
If value_of_a = "disable" Then
GetControl(ComboBox_1.Name + "_status").Enabled = False
End If
End Sub
Function GetControl(nameOfControl As String) As Control
For Each ctrl In Me.Controls
If ctrl.Name = nameOfControl Then
Return ctrl
End If
Next ctrl
Return Nothing
End Function
