How to loop through each label and change its borderstyle in Excel vba?

Question

I was trying to create a private procedure in a userform that can change the borderstyle of labels in certain frame.

Private Sub ShowBorder(LabelName As String, frame As MSForms.frame)
       Dim ctr As MSForms.Control
       
       Debug.Print frame.Controls.Count ' returns 7 which is correct as thers are only 7 labels in this frame
       
       For Each ctr In frame.Controls
              If TypeName(ctr) = "Label" And ctr.Tag = "BorderShow" And ctr.Name = LabelName Then
                     'change its borderstyle to 1
              Else
                     'change its borderstyle to 0
              End If
       
       Next ctr
End Sub

I have tested the loop actually loop through without any error but I just don't know how to set its property. ctr. doesn't has Borderstyle in Intellisense. Am I missing something in this code?? Thanks for the help.

Edit : I should mentioned that I call this procedure on Label mouse move event.

Answer 1

You can use the BorderStyle property of the Label object...

Private Sub ShowBorder(LabelName As String, frame As MSForms.frame)
       Dim ctr As MSForms.Control
       
       Debug.Print frame.Controls.Count ' returns 7 which is correct as thers are only 7 labels in this frame
       
       For Each ctr In frame.Controls
              If TypeName(ctr) = "Label" And ctr.Tag = "BorderShow" And ctr.Name = LabelName Then
                     'change its borderstyle to 1
                     ctr.BorderStyle = fmBorderStyleSingle
              Else
                     'change its borderstyle to 0
                     ctr.BorderStyle = fmBorderStyleNone
              End If
       
       Next ctr
End Sub

Note that the IntelliSense is not available in this case, since ctr is declared as a generic object instead of a label. To make it available, you can assign ctr to a variable that has been declared as Label, as @Shrotter has suggested.

Private Sub ShowBorder(LabelName As String, frame As MSForms.frame)
       Dim ctr As MSForms.Control
       Dim lbl As MSForms.Label
       
       Debug.Print frame.Controls.Count ' returns 7 which is correct as thers are only 7 labels in this frame
       
       For Each ctr In frame.Controls
              If TypeName(ctr) = "Label" Then
                Set lbl = ctr
                If lbl.Tag = "BorderShow" And lbl.Name = LabelName Then
                    'change its borderstyle to 1
                    lbl.BorderStyle = fmBorderStyleSingle
                Else
                    'change its borderstyle to 0
                    lbl.BorderStyle = fmBorderStyleNone
                End If
              End If
       Next ctr
End Sub