Excel VBA - 获取用户窗体标签标题属性的值

Excel VBA - Get the values of UserForm Label Caption Property

提问by

I have a UserForm with labels and textboxes. I want to loop through the Lables, get the value of their Caption property and enter that value as my column headers.

我有一个带有标签和文本框的用户窗体。我想遍历 Lables，获取其 Caption 属性的值并将该值输入为我的列标题。

Here is a snip of my code:

这是我的代码片段：

Dim element As Control

    For Each element In WQTR_Form.Controls
        If TypeName(element) = "Label" Then
            For Each Cell In Range.Rows(1).Cells
                Cells.Value = ***Label.caption***
            Next Cell
        End If
     Next element

What I'm trying to figure out is how to get ***Lable.Caption***.

我想弄清楚的是如何获得***Lable.Caption***.

I tried element.Captionbut the autocomplete list does not show Caption as a property for element because in the current context element is of the type Controls. What I need is how to get the value of Type Label.Caption for every Label on my form.

我试过了，element.Caption但自动完成列表没有将 Caption 显示为元素的属性，因为在当前上下文中元素的类型为 Controls。我需要的是如何为表单上的每个标签获取 Type Label.Caption 的值。

Of course, my entire approach may be wrong! If you have a suggestion of another way to do this please tell me. Thanks in advance for your help.

当然，我的整个方法可能是错误的！如果您有其他方法的建议，请告诉我。在此先感谢您的帮助。

UPDATE:*

更新：*

First off thanks for all your answers. Now, I'm having other weirdness. I mentioned it in response to Kostas K's comment below but I want to add it to the question. I'm getting a compile error "Variable Not Defined" for Cellin For Each Cell in Range.Rows(1).Cells.

首先感谢您的所有回答。现在，我有其他奇怪的事情。我在回应下面 Kostas K 的评论时提到了它，但我想将它添加到问题中。我收到Cellin的编译错误“未定义变量” For Each Cell in Range.Rows(1).Cells。

So, being a total novice I tried adding Dim Cell as Range. then I get a compile error that says the argument is not optional for Rangein For Each Cell in Range.Rows(1).Cells. Maybe that second erro is a result of adding the Dim? Maybe that whole line of code is just wrong!????

因此，作为一个完全新手，我尝试添加Dim Cell as Range. 然后我收到一个编译错误，说参数对于Rangein不是可选的For Each Cell in Range.Rows(1).Cells。也许第二个错误是添加 Dim 的结果？也许整行代码都错了！？？？？

I have thought about trying something like For Each Cell in Range("A1":Z1)but it that seems to defeat the purpose of Rows(1).Cells.

我曾想过尝试类似的事情，For Each Cell in Range("A1":Z1)但这似乎违背了Rows(1).Cells.

采纳答案by Dick Kusleika

There are a few sets of objects that inherit from a more generic object. Controlis a generic object and several object (TextBox, Label, ComboBox) inherit all the properties and methods of Controland then add more specific properties and methods that only apply to them.

有几组对象继承自更通用的对象。Control是一个通用对象，并且有几个对象（TextBox、Label、ComboBox）继承了 的所有属性和方法，Control然后添加了更多仅适用于它们的特定属性和方法。

For example, every control has the properties Top, Height, Left, and Width. So MS created a class called Controlthat contained those four properties. Then they created a class called Labelthat inherited from Controlso that Labelwould have those four properties too. Next, they added a .Captionproperty to the Labelclass - caption being specific to labels and not applicable to text boxes, list boxes, etc.

例如，每一个控件具有属性Top，Height，Left，和Width。因此，MS 创建了一个名为的类Control，其中包含这四个属性。然后他们创建了一个称为Label继承自的类，Control以便Label也具有这四个属性。接下来，他们向类添加了一个.Caption属性Label- 标题特定于标签，不适用于文本框、列表框等。

When you call lblMyLabel.Height, the interpreter goes through the Labelclass to the Controlclass to figure out what Heightis. The other side of that, and the part that you're experiencing, is that when you call ctlMyControl.Caption, the interpreter translates that into ctlMyControl.Object.Caption. the Objectproperty is the pathway to the Labelclass where the Captionproperty lives.

当您调用 时lblMyLabel.Height，解释器会遍历Label类到Control类以找出是什么Height。另一方面，您正在经历的部分是，当您致电时ctlMyControl.Caption，口译员将其翻译成ctlMyControl.Object.Caption. 该Object属性是路对Label其中类Caption物业的年限。

Because the interpreter figures out what kind of control it is at run time, Intellisense doesn't know at design time and can only give you the generic Controlproperties and methods.

因为解释器在运行时弄清楚它是哪种控件，Intellisense 在设计时不知道，只能给你通用的Control属性和方法。

To create column headers from labels, consider this code

要从标签创建列标题，请考虑以下代码

Private Sub CommandButton1_Click()

    Dim ctl As Control
    Dim lColCnt As Long

    For Each ctl In Me.Controls
        If TypeName(ctl) = "Label" Then
            lColCnt = lColCnt + 1
            Sheet1.Cells(1, lColCnt).Value = ctl.Caption
        End If
    Next ctl

End Sub

The Dimstatements are how you declare variables that you will be using later. The Mekeyword refers to the Userform that I'm coding in. If your code is in a standard module, you have to load the form and refer to it by name (like in your code), but if you working in the form's class module, you can use Me.

这些Dim语句是您声明稍后将使用的变量的方式。该Me关键字是指用户窗体，我在我的编码。如果你的代码是一个标准模块中，你必须加载形式（在你的代码等）按名称引用它，但如果你的窗体类模块的工作，您可以使用Me.

Every time I encounter a label, I increment a variable and then I use the Cellsproperty to write out the Caption. The first argument of Cellsis the row number (1 for the header row) and the second argument is for column number, which goes up by one for every label.

每次遇到标签时，我都会增加一个变量，然后使用该Cells属性写出Caption. 第一个参数Cells是行号（标题行为 1），第二个参数是列号，每个标签增加一个。

回答by wrslphil

You would use the control variable ie Element.Caption. You are wanting to loop through all "Element"'s in a Userform and check each Element's Caption.

您将使用控制变量 ie Element.Caption。您想要遍历用户表单中的所有“元素”并检查每个元素的标题。

Thanks :)

谢谢 ：）

回答by Mohamad

You can replace your Lable.Captionby element.BoundValueHope it can help you.

您可以取代你Lable.Caption通过element.BoundValue希望它可以帮助你。