Excel VBA - for each label / user form

Question

I have an Excel Userform and on it I have several Labels, some of which are on a Frame.

Is the following possible?

For each label [that is on the frame]
    [code]
Next label

If yes, how do you do that? Or can you group some of them and then control them as members of that group like

For each label in [groupname]
    [code]
Next label

? If yes, how do you do that? I tried to group them, but no success.

I would like that userform to serve as a user input interface with a dashboard-kind of thing on it too... Of course that would be much easier to do on a worksheet, but then you couldn't really make it look nice and userform-like, could you? (I.e. run as a small window without the menu or anything other than the area of what would be the "userform".)

What you're trying to achieve is possible thanks to the collections — Gino
– Gino, Commented Jun 14, 2015 at 15:20

Marek Stejskal · Accepted Answer · 2015-06-14 15:25:47Z

3

Try this for looping all labels within form:

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "Label" Then
        Debug.Print ctrl.Name
    End If
Next ctrl

And this for looping labels within a frame (name Frame1 in my case)

For Each ctrl In Me.Frame1.Controls
    If TypeName(ctrl) = "Label" Then
        Debug.Print ctrl.Name
    End If
Next ctrl

answered Jun 14, 2015 at 15:25

Marek Stejskal

2,7181 gold badge22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LinkBerest - SO sold our work Over a year ago

@metrazol Control type documentation has good information on this as well

Ivan Šilin-Shylin · Accepted Answer · 2017-08-09 09:28:41Z

0

To expand it a bit. Works also for MS Access as following to map the used labels.

Form OnOpen event :

 Private Sub Form_Open(Cancel As Integer)
 List_Labels Me.Form
 End Sub

Called Subroutine (i keep them in a separate module):

Sub List_Labels(ByRef frm As Form)
Dim ctrl As Control

For Each ctrl In frm.Controls
    If TypeName(ctrl) = "Label" Then
        Debug.Print frm.Name & " - " & ctrl.Name & " value: " & ctrl.Caption
    End If
Next ctrl
End Sub

answered Aug 9, 2017 at 9:28

Ivan Šilin-Shylin

12 bronze badges

Collectives™ on Stack Overflow

Excel VBA - for each label / user form

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related