How to change the caption on a dynamically created userform label?

KurtEdw

New Member
Joined
Feb 24, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi There,

I have a userform where i have a selected number of entries. In the picture attached its 8 but it can be 5,6,7 etc.

I have created a loop to create 8 entries, each with two labels and one textbox.

Once i start the program the numbers are going to rotate, and to do this the easiest way will be to just change the caption on these labels. However i am struggling to find the correct syntax to reference and change these dynamically created labels.

Any help would be appreciated with how to update these caption names multiple times once the label has been created.

Bonus points for the code to track when a number is entered and how to match it up to its correct entry (that will rotate).

Thanks Kurt

1614153810010.png


Code:

Private Sub UserForm_Initialize()

Call UserboxSetup

End Sub


Private Sub UserboxSetup()
NoEntry = 8
UserForm2.Height = 48 + (NoEntry + 1) * (24 + 6)


For i = 1 To NoEntry

RLabel = "Forms.Label.Runner." & CStr(i)
RNumber = Sheets("MAIN_Results").Cells(5 + i, 14).Value
RName = Sheets("MAIN_Results").Cells(5 + i, 15).Value
'NumberLabel = "Label"

'Label = "LabelNo" & CStr(i)
Set Label = UserForm2.Controls.Add("Forms.Label.1", "No" & CStr(i), True)
With Label
.Name = "LabelNo" & CStr(i)
.Caption = CStr(RNumber)
.Left = 18
.Width = 24
.Top = 48 + (i - 1) * 30
.Height = 24
End With


Set Label = UserForm2.Controls.Add("Forms.Label.1", "Testsdfsdf" & i, True)
With Label
.Caption = CStr(RName)
.Left = 60
.Width = 120
.Top = 48 + (i - 1) * 30
.Height = 24
End With
'MsgBox (Label.Name)

Set Label = UserForm2.Controls.Add("Forms.TextBox.1", "Testsdfsdf" & i, True)
With Label
.Left = 198
.Width = 54
.Top = 48 + (i - 1) * 30
.Height = 24
End With


Next i
'


End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
When you create those controls you assign them names from LabelNo1 to LabelNo8 (1st column), Testsdfsdf1 to Testsdfsdf8 (2nd column of labels), and Testsdfsdf1 to Testsdfsdf8 (textboxes)

You would be able to addres them using the syntax
VBA Code:
Me.Controls("Name").TheProperty = Value
"TheProperty" depends on control type and action to perform (eg: Caption for a Label; Text for a Textbox)

However you assign the same names both to the second column of label and the textbox, thus you will not be able to address the Textbox as the name first refers to the label.

Then, my suggestion:
A) modify the way you assign names to the controls. For example:
Code:
Set Label = UserForm2.Controls.Add("Forms.Label.1", "LabelA" & CStr(i), True)   'First label
' !!! Also remove the line .Name = "LabelNo" & CStr(i) from the code

Set Label = UserForm2.Controls.Add("Forms.Label.1", "LabelB" & i, True)         'Second label


Set Label = UserForm2.Controls.Add("Forms.TextBox.1", "TBox" & i, True)         'Textbox


B) at this point you can address labels and textbox using Controls("ControlName&Number"); eg:
Code:
Me.Controls("LabelB7").Caption = "Label text"

Me.Controls("TBox5").Text = "Set Value"       'set a text
myVar = Me.Controls("TBox5").Text             'read the text
Use "Me" if the code is within the userform code module; use "UserForm2" if the code is hosted by any othe vba module

Bye
 
Last edited:
Solution

KurtEdw

New Member
Joined
Feb 24, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Perfect thankyou Anthony!

I wasted 3 hours just trying to figure out the syntax.

Thankyou very much!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,824
Members
416,342
Latest member
BlueDevil12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top