Propagate labels from Worksheet

[Guest]

I was able to VB a label to show on a user form from a worksheet.

Private Sub UserForm_Click()

With Label1
.Caption = Worksheets("Sheet1").Range("B4") '
.AutoSize = True
.WordWrap = False
.Font.Name = "Times New Roman"
.Font.Size = 14
.Font.Bold = True
End With

End Sub

How do I propagate the labels with the Range title from the worksheet (Range
B4:B7) stopping at the first encountered empty cell? Also I have to click on
the form to show the actual label before the label caption shows-up. Could I
do it instantaneously?

Thanks a lot.

Ligaya

 

[Dave Peterson]

You have 4 labels (label1, label2, ..., label4)?

If yes:

Option Explicit
Private Sub UserForm_Initialize()

Dim iCtr As Long
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("b4:b7")

For iCtr = 1 To 4
With Me.Controls("label" & iCtr)
If myRng(iCtr).Value = "" Then
Exit For
'or
'.Caption = ""
Else
.Caption = myRng(iCtr).Text
.AutoSize = True
.WordWrap = False
.Font.Name = "Times New Roman"
.Font.Size = 14
.Font.Bold = True
End If
End With
Next iCtr

End Sub

 

[Guest]

You have 4 labels (label1, label2, ..., label4)?

If yes:

Option Explicit
Private Sub UserForm_Initialize()

Dim iCtr As Long
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("b4:b7")

For iCtr = 1 To 4
With Me.Controls("label" & iCtr)
If myRng(iCtr).Value = "" Then
Exit For
'or
'.Caption = ""
Else
.Caption = myRng(iCtr).Text
.AutoSize = True
.WordWrap = False
.Font.Name = "Times New Roman"
.Font.Size = 14
.Font.Bold = True
End If
End With
Next iCtr

End Sub

Dave,

Thanks. I'll try that approach .


Regards.

Ligaya