(Resolved)Loop through labels in VBA Excel2003

[M.Nolan]

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

[RobDog888]

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

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

[M.Nolan]

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

[RobDog888]

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

[M.Nolan]

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

[Webtest]

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

[M.Nolan]

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

[Webtest]

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

[M.Nolan]

Thanks for your help

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

[Webtest]

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:

    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.

[M.Nolan]

Webtest,

Thanks for your help.

... had to change:

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

Matt