(Resolved)Loop through labels in VBA Excel2003

This should be simple but I am struggling...

On my Form I have 5 labels named Label1, Label2...

I would like use a For...Each loop to set some position and caption properties for each of the labels.

Thanks

Matt

You can loop through the controls collection and check for the type of control it is.

VB Code:

Private Sub CommandButton1_Click()
    For Each lbl In Me.Controls
        If TypeName(lbl) = "Label" Then
            lbl.Caption = "Changed"
        End If
    Next
End Sub

Please bear with my ingnorance...

Cut/Paste your code to Commandbutton and run...

I get Type mis-match for Lbl ... is this a varfiable I need to declare?

while in debug says Lbl is Nothing

Thanks
Matt

What other controls are on your userform? I wrote this in Excel 2003 UserForm.

The Form has:

(5) Labels
(2) OptionButtons
(1) Listbox...Multicolumn
(1) Combobox

I want to loop thru using For ... Next not For...Each (my BAD Sorry!)

Thanks for your help

Matt

What is your hangup against the "For Each ... Next" loop? Is this a restriction on a class assignment or something?

I am using a For ... Next loop to set the column widths in the MultiColumnListBox, I would like to also set the placemenct and caption Properties for the Labels in the same loop ... Label(a).caption = "Hello"

But I'm not sure how to index thru the controls

...purely my own programming (lack of knowledge) limitation, just trying to learn.

Thanks
Matt

Post your relevant code ... especially your loop. Do you have Option Explicit set?

Thanks for your help

VB Code:

Dim CWidth(4) As Single
Dim a As Integer
Dim TotalWidth As Single
Dim ColumnHead(4) As String
 
Worksheets(2).Activate
TotalWidth = 0 'rest total width
 
For a = 0 To 4
 
 Columns(a + 1).Select
    ColumnHead(a) = Cells(1, (a + 1)) 'set column header name for labels
    Selection.EntireColumn.AutoFit 'auto fit column to widest value
    CWidth(a) = Selection.ColumnWidth * 5 'set uasable column width for list columns
    
    
                 
      
    With Label1 'NEED TO INDEX THIS LABEL TO SET LABEL 1-5 EXAMPLE...Label(a)
                .Caption = ColumnHead(a)
                .Width = CWidth(a)
                .Height = 20
                .LefT = LbData.LefT + TotalWidth
                .Top = 1
    End With
    
TotalWidth = TotalWidth + CWidth(a) 'add to running total for listbox
    
    
Next a

I do not set option explicit

WARNING: I don't use controls or forms in Excel so I'm on thin ice here, but this should get you closer to a solution.

I think you can scan the controls and parse 'handles' for the controls into an array ... do this early in your program:

Code:

---

    Dim LabelList(4) As Control
    Dim lbl As Control
    Dim i As Integer
   
    'Scan the controls for Labels
    For Each lbl In Me.Controls
        'Process only the Label Controls
        If TypeName(lbl) = "Label" Then
            'This is a Label ... Parse the numeric ID to position it in the List
            'Assumes format:  Labeln  where n is an integer
            i = CInt(Mid$(lbl.Caption, 6, 1)) - 1
            Set LabelList(i) = lbl    'Thanks to M. Nolan ... (edited)
        End If
    Next lbl

---

Now you should have an indexed array of control handles "LabelList(n).Caption" to use in your loop.

Webtest,

Thanks for your help.

... had to change:

LabelList(i) = lbl to Set LabelList(i) = lbl

Matt