Experts Exchange connects you with the people and services you need so you can get back to work.
Private Sub UserForm_Initialize()
Dim i As Integer
Dim ctrl As Control
i = 2
For Each ctrl In Me.Controls
If ctrl.Name = "Label" & i Then
ctrl.Caption = ThisWorkbook.Sheets("Data").Range("A" & i + 17)
i = i + 1
Open in new window
UserForm1.Label1.Caption = Range("A19").Value
UserForm2.Label2.Caption = Range("A20").Value
'do this through the last caption
UserForm1.Label20.Caption = Range("A38").Value
Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Private Sub Userform_Initialize()
Dim oControl As Control
Dim oRange As Range
Dim oCell As Variant, oRetVal As Variant
oRetVal = InputBox("Populated Cells are A19 thru A38" & vbCr _
& "Please enter a valid Range (xx:xx) for control captions" & vbCr & vbCr _
& "Example: A19:A38 or A22:A28 or A30:A38, etc.")
If oRetVal = vbCancel Or oRetVal = "" Then Exit Sub
Set oRange = ThisWorkbook.Sheets("Data").Range(oRetVal)
For Each oControl In Me.Controls
For Each oCell In oRange.Cells
If oControl.Tag = oCell.Column & oCell.Row Then
If oCell.Text <> "" Then oControl.Caption = oCell.Text
Dim I As Long
Dim rngCaption As Range
Dim lblName As String
Set rngCaption = Worksheets("Data").Range("A18"
For I = 1 To 20
lblName ="Label" & I ' change to lblName = "Label" & Format(I, "00") if the labels are named Label01 Label02, etc
Me.Controls(lblName).Caption = rngCaption.Offset(I)
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.