循环组合框 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() 变量在其他地方被声明为 public

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)，用户窗体在启动时自举，全局变量也消失了，这是一个很好的事物.

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 中的项目.这不应该发生，因为组合框从预先选择的第一个条目开始.

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屋！