通过更改名称值，Excel VBA userform通过组合框循环(Excel VBA userform loop through combo boxes by changing name value)

Excel用户窗体中有四个组合框，并带有这些值引用：

Me.cbDesc1.Value Me.cbDesc2.Value Me.cbDesc3.Value Me.cbDesc4.Value

我想要做的是循环这些组合框的值，以便数字1-4被一个变量改变，像这样...

For j = 1 to 4 cell_j_in_sheet = "Me.cbDesc" & j & ".Value" Next j

我在单元格中看到的是字符串（例如）“Me.cbDesc1.Value”，而不是在组合框中选择的实际值。 我如何指示VBA评估Me.cbDescj.Value并通过每次迭代输出cbDescj的当前内容？

如果有更好的方法来做到这一点，请告知。

I have four combo boxes in an Excel userform with these value references:

Me.cbDesc1.Value Me.cbDesc2.Value Me.cbDesc3.Value Me.cbDesc4.Value

What I want to do is loop through these combo box values so that the numbers 1-4 are changed with a variable, something like this...

For j = 1 to 4 cell_j_in_sheet = "Me.cbDesc" & j & ".Value" Next j

What I see in my cell is the string (e.g.) "Me.cbDesc1.Value" and not the actual value selected in the combo box. How do I instruct VBA to evaluate Me.cbDescj.Value and output the current contents of cbDescj through each iteration?

If there's better way to do this, please advise.

最满意答案

您可以使用表单的Controls属性：

For j = 1 to 4 cell_j_in_sheet = Me.Controls("cbDesc" & j).Value Next j

You can use the form's Controls property:

For j = 1 to 4 cell_j_in_sheet = Me.Controls("cbDesc" & j).Value Next j通过更改名称值，Excel VBA userform通过组合框循环(Excel VBA userform loop through combo boxes by changing name value)

Excel用户窗体中有四个组合框，并带有这些值引用：

Me.cbDesc1.Value Me.cbDesc2.Value Me.cbDesc3.Value Me.cbDesc4.Value

我想要做的是循环这些组合框的值，以便数字1-4被一个变量改变，像这样...

For j = 1 to 4 cell_j_in_sheet = "Me.cbDesc" & j & ".Value" Next j

我在单元格中看到的是字符串（例如）“Me.cbDesc1.Value”，而不是在组合框中选择的实际值。 我如何指示VBA评估Me.cbDescj.Value并通过每次迭代输出cbDescj的当前内容？

如果有更好的方法来做到这一点，请告知。

I have four combo boxes in an Excel userform with these value references:

Me.cbDesc1.Value Me.cbDesc2.Value Me.cbDesc3.Value Me.cbDesc4.Value

What I want to do is loop through these combo box values so that the numbers 1-4 are changed with a variable, something like this...

For j = 1 to 4 cell_j_in_sheet = "Me.cbDesc" & j & ".Value" Next j

What I see in my cell is the string (e.g.) "Me.cbDesc1.Value" and not the actual value selected in the combo box. How do I instruct VBA to evaluate Me.cbDescj.Value and output the current contents of cbDescj through each iteration?

If there's better way to do this, please advise.

最满意答案

您可以使用表单的Controls属性：

For j = 1 to 4 cell_j_in_sheet = Me.Controls("cbDesc" & j).Value Next j

You can use the form's Controls property:

For j = 1 to 4 cell_j_in_sheet = Me.Controls("cbDesc" & j).Value Next j