如何在VBA中检索动态创建的ComboBox的名称？(How to retrieve name of dynamically created ComboBox in VBA?)

如何在VBA中检索动态创建的ComboBox的名称？(How to retrieve name of dynamically created ComboBox in VBA?)

我想引用我使用循环创建的ComboBoxes的值，但我不知道它们的名字是什么。 我怎样才能到他们的名字？

Sub addLabel() Dim theLabel As Object Dim theRanker As Object Dim labelCounter As Long Dim RowCount As Integer RowCount = Sheets("Overview").Range("A" & ).End(xlUp).Row For labelCounter = 1 To RowCount Set theRanker = CriteriaPairwiseForm.Controls.Add("Forms.ComboBox.1", "Rating" & labelCounter, True) With theRanker .Left = 20 .Width = 150 .Top = 0 * labelCounter .AddItem "Equal Importance" .AddItem "Moderate Importance" .AddItem "Strong Importance" .AddItem "Very Strong Importance" .AddItem "Extreme Importance" End With Set theLabel = CriteriaPairwiseForm.Controls.Add("Forms.Label.1", "CriteriaRank" & labelCounter, True) With theLabel .caption = Cells(labelCounter, 1).Value .Left = 200 .Width = 150 .Top = 0 * labelCounter End With ext labelCounter End Sub

I would like to reference the value of ComboBoxes I created using a loop, but I do not know what their name is. How can I find their name?

Sub addLabel() Dim theLabel As Object Dim theRanker As Object Dim labelCounter As Long Dim RowCount As Integer RowCount = Sheets("Overview").Range("A" & ).End(xlUp).Row For labelCounter = 1 To RowCount Set theRanker = CriteriaPairwiseForm.Controls.Add("Forms.ComboBox.1", "Rating" & labelCounter, True) With theRanker .Left = 20 .Width = 150 .Top = 0 * labelCounter .AddItem "Equal Importance" .AddItem "Moderate Importance" .AddItem "Strong Importance" .AddItem "Very Strong Importance" .AddItem "Extreme Importance" End With Set theLabel = CriteriaPairwiseForm.Controls.Add("Forms.Label.1", "CriteriaRank" & labelCounter, True) With theLabel .caption = Cells(labelCounter, 1).Value .Left = 200 .Width = 150 .Top = 0 * labelCounter End With ext labelCounter End Sub

最满意答案

最简单的方法是使用从.Add方法获得的引用来命名它们。 您已经遍历了一系列值，因此请使用循环计数器来构建对象名称。 这样做的好处是还有与标签所来自的行相对应的名称：

Sub addLabel() Dim theLabel As Object Dim theRanker As Object Dim labelCounter As Long Dim RowCount As Integer RowCount = Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Row For labelCounter = 1 To RowCount Set theRanker = CriteriaPairwiseForm.Controls.Add("Forms.ComboBox.1", _ "Rating" & labelCounter, True) With theRanker .Left = 20 .Width = 150 .Top = 0 * labelCounter .AddItem "Equal Importance" .AddItem "Moderate Importance" .AddItem "Strong Importance" .AddItem "Very Strong Importance" .AddItem "Extreme Importance" .ame = "Ranker" & labelCounter End With Set theLabel = CriteriaPairwiseForm.Controls.Add("Forms.Label.1", _ "CriteriaRank" & labelCounter, True) With theLabel .Caption = Cells(labelCounter, 1).Value .Left = 200 .Width = 150 .Top = 0 * labelCounter End With ext labelCounter End Sub

您可以使用相同的方法稍后从中获取值（或通过连接名称按行索引来解决它们）。 您可以通过创建名为“CriteriaPairwiseForm”的UserForm来验证这一点，并在Sub addLabel之外的表单中添加以下代码：

Option Explicit Private Sub UserForm_Initialize() addLabel End Sub Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean) GetValues End Sub Private Sub GetValues() Dim labelCounter As Long Dim RowCount As Integer Dim message As String RowCount = Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Row For labelCounter = 1 To RowCount message = "Ranker" & labelCounter & " contains:" & vbCrLf & _ CriteriaPairwiseForm.Controls("Ranker" & labelCounter).Text MsgBox message ext labelCounter End Sub

The easiest way to do this is to use the reference you get from the .Add method to name them. You're already looping through a range of values, so use the loop counter to build object names. This has the advantage of also having the names corresponding to row that the label originates from:

Sub addLabel() Dim theLabel As Object Dim theRanker As Object Dim labelCounter As Long Dim RowCount As Integer RowCount = Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Row For labelCounter = 1 To RowCount Set theRanker = CriteriaPairwiseForm.Controls.Add("Forms.ComboBox.1", _ "Rating" & labelCounter, True) With theRanker .Left = 20 .Width = 150 .Top = 0 * labelCounter .AddItem "Equal Importance" .AddItem "Moderate Importance" .AddItem "Strong Importance" .AddItem "Very Strong Importance" .AddItem "Extreme Importance" .ame = "Ranker" & labelCounter End With Set theLabel = CriteriaPairwiseForm.Controls.Add("Forms.Label.1", _ "CriteriaRank" & labelCounter, True) With theLabel .Caption = Cells(labelCounter, 1).Value .Left = 200 .Width = 150 .Top = 0 * labelCounter End With ext labelCounter End Sub

You can use the same method to get the values from them later (or address them by row index by concatenating the name). You can verify this by creating a UserForm named "CriteriaPairwiseForm", and putting the following code in the form in addition to Sub addLabel:

Option Explicit Private Sub UserForm_Initialize() addLabel End Sub Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean) GetValues End Sub Private Sub GetValues() Dim labelCounter As Long Dim RowCount As Integer Dim message As String RowCount = Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Row For labelCounter = 1 To RowCount message = "Ranker" & labelCounter & " contains:" & vbCrLf & _ CriteriaPairwiseForm.Controls("Ranker" & labelCounter).Text MsgBox message ext labelCounter End Sub