Short code to set caption on labels based on cell values

[sujittalukde]

I am using the following technique to set the caption of labels on a user form:
Label1.caption = Range(?A2?).value
Label2.caption = Range(?A3?).value
And so on
Now I have a user form where labels are more in numbers. Though this method works well Can I use a short code which will put the headers of Row 1 as caption of labels?

[dominicb]

Good afternoon sujittalukde

Something like this should work OK. You haven't specified how you want to run through the range, so this example depends on a range being selected first, but is easy enough to adapt to pick up a predefined range :


Sub test()
Dim UsrCell, A As Long, B As String
A = 1
For Each UsrCell In Selection
B = "Label" & A
UserForm1.Controls(B).Caption = UsrCell.Value
A = A + 1
Next UsrCell
End Sub

HTH

DominicB

[sujittalukde]

Thanks dominicb, I have not tested the same but seems that will work well.

[Charlize]

'*** Module code
Sub show_form()
UserForm1.Show
End Sub
Public Sub Caption_Labels(wsname As String, UF As UserForm, _
No_Labels As Long)
Dim ws As Worksheet
Dim vloop As Long
If wsname <> vbNullString Then
Set ws = Worksheets(wsname)
Else
Set ws = ActiveSheet
End If
For vloop = 1 To No_Labels
UF.Controls("Label" & vloop).Caption = ws.Cells(1, vloop).Value
Next vloop
End Sub
'*** End module

Private Sub UserForm_Initialize()
'This is for the userform code
Call Caption_Labels("Database", Me, 4)
End SubCharlize