How to add a dynamic label in a VBA userform and assign a specific cell value?

Question

In the code below I am adding a label "dLblQty" on the click of save button on the userform. And this button needs to be assigned a value from Cell A27 then A28 and so on in each iteration of button press. But this gives a Runtime error '438': Object doesn't support this property or method. Please suggest correct way of doing this.

Below is the snippet:

Dim strCap As String

strCap = Worksheets("Tax Invoice - Intra State").Range("A1").Value

Dim dLblQty As Control
Set dLblQty = UserForm1.Controls.Add("Forms.Label.1", "dLblQty", True)
With dLblQty
    .Top = 20
    .Left = 20
    .MultiLine = True
    .Height = 100
    .Caption = strCap
End With

Why change the cell you take the value from on each click? If it is the last value in that column, look into using the concept of Lastrow. — Luuklag, Aug 28 '17 at 13:13
`.EnterKeyBehavior` only seems to be a valid property for TextBox controls: https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/enterkeybehavior-property — barvobot, Aug 28 '17 at 13:13
@J.Fox Yes I agree. So removed .Enterkeybehavior property now. But my question still exists. — curiousBrain, Aug 28 '17 at 13:29
When I tried your code without `.EnterKeyBehavior = True` in it, it ran fine without erroring. Were you still encountering an error? — barvobot, Aug 28 '17 at 13:46
If i am correct, you are adding Controls to a Userform, look here for an example to make it: [Creating form programmatically in the module using vba](https://stackoverflow.com/questions/11519345/creating-form-programmatically-in-the-module-using-vba) — danieltakeshi, Aug 28 '17 at 13:47

score 0 · Answer 1 · answered Aug 28 '17 at 13:49

0

The error is thrown because the Label control doesn't have a multiline property or method. The rest of the snippet functions perfectly.

answered Aug 28 '17 at 13:49

Rik Sportel

2,661
1
14
24

Weirdly enough, it ran fine for me with `.MultiLine = True` still in there when I tried it earlier, but *now* it's erroring. I wonder why it would error sometimes but not others? – barvobot Aug 28 '17 at 13:55

score 0 · Answer 2 · edited Sep 06 '17 at 05:15

The code certainly gives no errors on removing multiline and enterkeybehavior as these are textbox properties. But this didn't solve my problem of adding a dynamic label and dynamically assigning a cell value to it.

After trying a few times, I understood what I was doing wrong. I declared my label as an object of MSForms.label class and this did the trick. Below is the solution:

Dim strCap As String
Dim dLblItem As MSForms.Label

strCap = Worksheets("Tax Invoice - Intra State").Range("B1").Value

Set dLblItem = UserForm1.Controls.Add("Forms.Label.1", "dLblItem", True)
With dLblItem
    .Top = 20
    .Left = 20
    '.MultiLine = True
    '.EnterKeyBehavior = True
    .Height = 100
    .Caption = strCap
    .BackColor = &HFF&
    .ForeColor = &H8000&
End With

How to add a dynamic label in a VBA userform and assign a specific cell value?

2 Answers2