问题描述
我遇到了无法找到正确语法的问题.我正在处理的代码如下所示:
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!