通过名称遍历许多ComboBoxes [英] Looping through many ComboBoxes by name

问题描述

我已经退出了很多ComboBox对象，我试图通过循环显示ComboBox的值：

 对于i = 1到100 
 MsgBox（ComboBox&.Text）
下一个
  

当然，这段代码由于引号不起作用，但是当我删除它们时，我有一个编译错误。

如何执行此操作？

解决方案

如果所涉及的对象是 ActiveX 或表单控件对象。

处理两个对象类型，如果对象名称不是 ComboBox * ，尝试这个

  Sub Demo（）
 Dim ws As Worksheet 
 Dim shp As Shape 
 Dim cb As ComboBox 
 
设置ws = ActiveSheet 
 
对于每个shp在ws.Shapes 
用shp 
选择案例.Type 
案例msoFormControl 
 If .FormControlType = xlDropDown Then 
如果.ControlFormat.Value = 0然后
 MsgBox .Name& =
 Else 
 MsgBox .Name& =& .ControlFormat.List（.ControlFormat.Value）
 End If 
 End If 
 Case msoOLEControlObject 
如果.OLEFormat.progID =Forms.ComboBox.1然后
设置cb = .OLEFormat.Object.Object 
 MsgBox cb.Name& =& cb.Value 
结束如果
结束选择
结束
下一个
 End Sub 
  

I have quit a lot ComboBox objects, and I am trying to display the value of the ComboBox by looping through them:

For i=1 to 100
    MsgBox ("ComboBox" & i & ".Text")
Next

Of course this code is not working because of the quotes, but when I remove them, then I have a compilation error.

How do I do this?

解决方案

The OP does not state if the objects in question are ActiveX or Form Control objects.

To handle both object types, and if the objects names are not ComboBox*, try this

Sub Demo()
    Dim ws As Worksheet
    Dim shp As Shape
    Dim cb As ComboBox

    Set ws = ActiveSheet

    For Each shp In ws.Shapes
        With shp
            Select Case .Type
                Case msoFormControl
                    If .FormControlType = xlDropDown Then
                        If .ControlFormat.Value = 0 Then
                            MsgBox .Name & " = "
                        Else
                            MsgBox .Name & " = " & .ControlFormat.List(.ControlFormat.Value)
                        End If
                    End If
                Case msoOLEControlObject
                    If .OLEFormat.progID = "Forms.ComboBox.1" Then
                        Set cb = .OLEFormat.Object.Object
                        MsgBox cb.Name & " = " & cb.Value
                    End If
            End Select
        End With
    Next
End Sub

这篇关于通过名称遍历许多ComboBoxes的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！