I have a sheet like this :
And I have a user form which will dynamically add labels based on data I have in a column or row. The issue is that if its all aligned one by one, then I am able to generate dynamic labels. But I need to generate the first row of this sheet. So my labels will be A,B,C,D,E etc
This is my code :
Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = "Sheet1!C8:C" & Range("C" & Rows.Count).End(xlUp).Row
number = Range("E1").Value
Dim lblL1 As Control
Dim cell As Range
Dim Rng As Range
For i = 1 To number
Set lblL1 = Controls.Add("Forms.Label.1")
With lblL1
.Name = "lbl" & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With
Next i
Set Rng = Range("O6", "ED6")
'This is what I added to achieve this. But I am getting the last one displayed and not others
For Each cell In Rng.Cells
If cell.Value <> vbNullString Then
With lblL1
.Caption = cell.Value
End With
End If
Next cell
End Sub
If the items in the row/column are adjacent then I can use something like this :
Private Sub UserForm_Initialize()
Me.ComboBox1.RowSource = "Sheet1!C8:C" & Range("C" & Rows.Count).End(xlUp).Row
number = Range("E1").Value
Dim lblL1 As Control
Dim cell As Range
Dim Rng As Range
For i = 1 To number
Set lblL1 = Controls.Add("Forms.Label.1")
With lblL1
.Caption = "Label”" & i
.Name = "lbl" & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With
Next i
Dim q As Long
For q = 1 To number
Controls("lbl" & q) = Cells(6, q).Value
Next q
End Sub
This is what I tried which gives me the last item in that row only :
For Each cell In Rng.Cells
If cell.Value <> vbNullString Then
With lblL1
.Caption = cell.Value
End With
End If
Next cell
What has to be done to show all data in that row as each label (one by one) dynamically. Here each data is 3 cells apart.
Without reading all your code, I'm trying to approach this from a logical point of view. You could
The last approach would make most sense, because it tackles the issue from a data architecture point of view instead of building workarounds for bad data layouts.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments