通过组合框VBA循环 [英] Loop through Comboboxes VBA
问题描述
我被困在找不到正确语法的地方. 我正在处理的代码如下:
I am stuck at something which I can not find the correct syntac for. The code I am working on looks like this:
Public Function Initializecolors()
'initialize the colors by setting them with the help of the webpage:
'http://dmcritchie.mvps.org/excel/colors.htm
'example. Black is the first color
'0(since black is the first index selection) + 1 = 1
Color(0) = 1 'Black
Color(1) = 3 'Red
Color(2) = 4 'Green
Color(3) = 5 'Blue
Color(4) = 7 'Magenta
Color(5) = 9 'Brown
'Study each combobox of colors and if some color
'is selected, initialize it directly to the Indexvariable
Select Case UserForm2.ComboBox7.ListIndex
Case 0
colorComboBoxIndex(0) = Color(0)
Case 1
colorComboBoxIndex(0) = Color(1)
Case 2
colorComboBoxIndex(0) = Color(2)
Case 3
colorComboBoxIndex(0) = Color(3)
Case 4
colorComboBoxIndex(0) = Color(4)
Case 5
colorComboBoxIndex(0) = Color(5)
End Select
Select Case UserForm2.ComboBox8.ListIndex
Case 0
colorComboBoxIndex(1) = Color(0)
Case 1
colorComboBoxIndex(1) = Color(1)
Case 2
colorComboBoxIndex(1) = Color(2)
Case 3
colorComboBoxIndex(1) = Color(3)
Case 4
colorComboBoxIndex(1) = Color(4)
Case 5
colorComboBoxIndex(1) = Color(5)
End Select
Select Case UserForm2.ComboBox9.ListIndex
Case 0
colorComboBoxIndex(2) = Color(0)
Case 1
colorComboBoxIndex(2) = Color(1)
Case 2
colorComboBoxIndex(2) = Color(2)
Case 3
colorComboBoxIndex(2) = Color(3)
Case 4
colorComboBoxIndex(2) = Color(4)
Case 5
colorComboBoxIndex(2) = Color(5)
End Select
Select Case UserForm2.ComboBox10.ListIndex
Case 0
colorComboBoxIndex(3) = Color(0)
Case 1
colorComboBoxIndex(3) = Color(1)
Case 2
colorComboBoxIndex(3) = Color(2)
Case 3
colorComboBoxIndex(3) = Color(3)
Case 4
colorComboBoxIndex(3) = Color(4)
Case 5
colorComboBoxIndex(3) = Color(5)
End Select
Select Case UserForm2.ComboBox11.ListIndex
Case 0
colorComboBoxIndex(4) = Color(0)
Case 1
colorComboBoxIndex(4) = Color(1)
Case 2
colorComboBoxIndex(4) = Color(2)
Case 3
colorComboBoxIndex(4) = Color(3)
Case 4
colorComboBoxIndex(4) = Color(4)
Case 5
colorComboBoxIndex(4) = Color(5)
End Select
Select Case UserForm2.ComboBox12.ListIndex
Case 0
colorComboBoxIndex(5) = Color(0)
Case 1
colorComboBoxIndex(5) = Color(1)
Case 2
colorComboBoxIndex(5) = Color(2)
Case 3
colorComboBoxIndex(5) = Color(3)
Case 4
colorComboBoxIndex(5) = Color(4)
Case 5
colorComboBoxIndex(5) = Color(5)
End Select
End Function
然后将这些值应用于下面的代码，该代码设置颜色的值
These values are then applied to the code below which sets the value of the color
...For j = 7 to 12
If colorComboBoxIndex(j) > -1 Then
...
.Border.ColorIndex = colorComboBoxIndex(j)
....
它的功能是这样的.我不需要调试帮助.只是如何使用for循环制作第一个代码块的语法.
It functions like this. I do not need help with debug. Just the syntax of how to make the first code block with just a for loop. something in the art of:
for j = 0 to 5
Select Case UserForm2.ComboBox(j).ListIndex
我知道此语法不存在，并且尝试使用 Me.Controls 运气不错，但是出现了编译错误.
I know this syntax does not exist and have tried my luck with Me.Controls but had compile errors.
将寻求任何帮助.
亲切的问候，
马里奥
Any kind of help would be appriciated.
Kind regards,
Mario
p.s Color()和coloComboBox()变量在其他地方声明为公共
p.s The Color() and coloComboBox() variables are declared public somewhere else
推荐答案
您正在寻找的语法是
Me.Controls.Item("ComboBox" & j)
但是让用户控件保留其随机默认名称是不好的风格.从一开始就给它们适当的名称，因此在VBA代码中对它们的引用实际上可能是有意义的.
But leaving the user controls at their random default names is bad style. Give them appropriate names right from the start, so references to them in VBA code can actually be meaningful.
这是一种更完善的方法:在包含颜色组合框的用户窗体中，编辑其属性并将其命名为ColorBox_0至ColorBox_4.然后，在该用户窗体的代码中，添加以下代码:
Here is a more refined approach: In your UserForm that contains the color combo boxes, edit their properties and name them ColorBox_0 through ColorBox_4. Then, in the code for that UserForm, add this:
Option Explicit
Private Const COLOR_BOX_COUNT As Integer = 4 ' actually that's 5, as we count from 0
Private Sub UserForm_Initialize()
Dim cmb As ComboBox, i As Integer
' Prepare color combo boxes with actual RGB color codes and names
For i = 0 To COLOR_BOX_COUNT
Set cmb = Me.Controls.Item("ColorBox_" & i)
cmb.Clear
cmb.ColumnCount = 2
cmb.ColumnHeads = False
cmb.ColumnWidths = "0;"
cmb.AddItem "000000": cmb.Column(1, 0) = "Black"
cmb.AddItem "FF0000": cmb.Column(1, 1) = "Red"
cmb.AddItem "00FF00": cmb.Column(1, 2) = "Green"
cmb.AddItem "0000FF": cmb.Column(1, 3) = "Blue"
cmb.AddItem "FF00FF": cmb.Column(1, 4) = "Magenta"
cmb.AddItem "7C2927": cmb.Column(1, 5) = "Brown"
cmb.MatchRequired = True
cmb.Value = cmb.List(0) ' pre-select first entry
Next i
End Sub
Public Function GetSelectedColors() As Long()
Dim cmb As ComboBox, i As Integer
Dim result(COLOR_BOX_COUNT) As Long
For i = 0 To COLOR_BOX_COUNT
Set cmb = Me.Controls.Item("ColorBox_" & i)
If IsNull(cmb.Value) Then
result(i) = -1
Else
result(i) = GetColor(cmb.Value)
End If
Next i
GetSelectedColors = result
End Function
请注意GetSelectedColors()如何返回颜色数组.
Note how GetSelectedColors() returns an array of colors.
还有一个辅助功能，可将RGB颜色代码转换为数字(颜色是VBA中的Long值，因此，如果您想以某种方式实际使用颜色，例如设置控件的BackColor，您实际上可以直接使用该值):
There also is a helper function to convert RGB color codes to a number (colors are Long values in VBA, so if you would like to actually use the color in some way, like setting the BackColor of a control, you can actually use that value straight-away):
Function GetColor(rgb As Variant) As Long
If Len(rgb) = 6 And IsNumeric("&H" & rgb) Then
GetColor = CLng("&H" & Right(rgb, 2) & Mid(rgb, 3, 2) & Left(rgb, 2))
End If
End Function
有了这一切，您就不再需要魔术常数(1 = Black, 3 = Red)，UserForm会在开始时自行引导，并且全局变量也消失了，这是一件好事.
With all this, you don't need magic constants (1 = Black, 3 = Red) anymore, the UserForm bootstraps itself on start and global variables are gone as well, which is a good thing.
我唯一达成的约定是，颜色值-1表示用户尚未在ComboBox中选择一个项目.这不应该发生，因为ComboBox会从预先选择的第一个条目开始.
The only convention I made is that a color value of -1 means that the user has not selected an item in the ComboBox. This should not happen as the ComboBoxes start with the first entry pre-selected.
现在您可以直接获取所选颜色
Now you can get the selected colors directly
Private Sub TestButton_Click()
Dim colors() As Long
colors = Me.GetSelectedColors
' do something with them'
End Sub
或者也许
Private Sub ColorBox_1_Change()
ColorLabel_1.BackColor = GetColor(ColorBox_1.Value)
End Sub
这篇关于通过组合框VBA循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
