Often a label control is bound to a textbox or similar other control. I know that there is a way to access the label through code, but I do not remember how to do it. Can anyone help? Maybe something like: Me.txtName.Child!lblName.Value Or Me.txtName.Parent!lblName.Value I will use this when I am enumerating through the controls on a form for validation purposes. Then I want to use the label for a control as information in the error message to show the user where the error is.

Reputation: 83

How to access the label of a control in code

Often a label control is bound to a textbox or similar other control. I know that there is a way to access the label through code, but I do not remember how to do it.

Can anyone help?

Maybe something like:

Me.txtName.Child!lblName.Value

Me.txtName.Parent!lblName.Value

I will use this when I am enumerating through the controls on a form for validation purposes. Then I want to use the label for a control as information in the error message to show the user where the error is.

Upvotes: 7

Answers (6)

Jason T. Fleishman

Reputation: 11

After some research, error handling for controls with no associated label seems easy. Since common controls - TextBoxes, ComboBoxes, ListBoxes, CheckBoxes - have a maximum of 1 item in their controls collection, the associated label, counting the TextBox controls reveals if there is an associated label. For example, with a textbox called myAddress: IIf(myAddress.Controls.Count > 0,"There is an associated label","Unfortunately there is no label"). The foregoing has been tested by me and functions correctly.

I credit the comment of Laurent, written in French, on the website of Daniel Pineault of Developers Hut. See https://www.devhut.net/ms-access-vba-determine-controls-associated-label/

Upvotes: 0

Perry Sugerman

Reputation: 178

Here is some code that I wrote to rename the labels associated with OptionButtons. A label has a parent property that points to the control it labels. The function is pretty general while the subroutine is written for OptionButtons. This code should work for almost anything except when the label is not associated, I have not provided any recovery for that.

Public Function paNameControlLabel(FormName As String, ControlName As String) As String Dim frm As Form Dim ctl As Control Dim ctlLabel As Control Dim ctlParent As Control

Set frm = Forms(FormName)
For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acLabel
            If ctl.Parent.Name = ControlName Then
                Debug.Print "Label " & ctl.Name & " Renamed to lbl" & ControlName
                ctl.Name = "lbl" & ControlName
                paNameControlLabel = ctl.Name
            End If
    End Select
Next ctl

End Function Public Sub paNameOptionButtonLabels(FormName As String) Dim frm As Form Dim ctl As Control

Set frm = Forms(FormName)
For Each ctl In frm.Controls
    If ctl.ControlType = acOptionButton Then
        Debug.Print paNameControlLabel(FormName, ctl.Name)
    End If
Next ctl
Set frm = Nothing

End Sub

Upvotes: 1

Francis Omoruto

Reputation: 76

Probably late, but I just struggled with this, and what worked was to check the type of control to make sure it supports labels, and then use

ctl.Properties(3) ' For the label name
Forms(ctl.Form).Controls(ctl.Properties(3)).Caption ' For the label text

In the Immediate window, using a text index instead of the magic number: ctl.properties("LabelName") worked.

At first I looked for the label among the properties of the textbox and struck out. I then looked among the controls of the form and found it there.

Hope this helps someone out!

Upvotes: 3

David-W-Fenton

Reputation: 23067

@Astander has provided the correct answer, but keep in mind that not all controls have the same kind of Controls collections.

TextBoxes, ComboBoxes, ListBoxes, CheckBoxes have a maximum of 1 item in their controls collection (the attached label), but if the label isn't attached, they won't even have that, so .Controls(0) will throw an error.

An Option Group has multiple controls, the label and the option button or toggle buttons inside the frame. When you drop an option group on a form from the form tools toolbar, the frame is created with an attached label, so it will be the control with index 0. But if, for instance, you delete the default label, add option buttons and then add back a label, it will not be index 0, but index .Controls.Count - 1.

So, for the caption of an option group lable, you either want to be careful that if you delete the default label, you also delete the controls inside the frame after you add the label back. If that's not the case, you need to name the label and refer to it by name, because the labels for the option/toggle buttons are part of the option group's Controls collection (this surprised me -- I expected them be only in the Controls collection of the option/toggle button to which they were attached).

To avoid this problem, I can imagine convoluted code where you looped through the option group's Controls collection looking for the labels attached to the option/toggle buttons, and then looped through the option group's Controls collection a second time, this time looking only at the labels. Something like this:

  Public Function FindOptionGroupLabel(ctlOptionGroup As Control) As Control
    Dim ctl As Control
    Dim strOptionToggleLabels As String

    If ctlOptionGroup.ControlType <> acOptionGroup Then
       MsgBox ctlOptionGroup.Name & " is not an option group!", _
         vbExclamation, "Not an option group"
       Exit Function
    End If
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acOptionButton, acToggleButton
          If ctl.Controls.Count = 1 Then
             strOptionToggleLabels = strOptionToggleLabels & " " & ctl.Controls(0).Name
          End If
      End Select
    Next ctl
    strOptionToggleLabels = strOptionToggleLabels & " "
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acLabel
          If InStr(" " & strOptionToggleLabels & " ", ctl.Name) = 0 Then
             Set FindOptionGroupLabel = ctl
          End If
      End Select
    Next ctl
    Set ctl = Nothing
  End Function

Now, this breaks if there is no label attached, so it would probably make more sense for it to return the label name, rather than the control reference:

  Public Function FindOptionGroupLabel(ctlOptionGroup As Control) As String
    Dim ctl As Control
    Dim strOptionToggleLabels As String

    If ctlOptionGroup.ControlType <> acOptionGroup Then
       MsgBox ctlOptionGroup.Name & " is not an option group!", _
         vbExclamation, "Not an option group"
       Exit Function
    End If
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acOptionButton, acToggleButton
          If ctl.Controls.Count = 1 Then
             strOptionToggleLabels = strOptionToggleLabels & " " & ctl.Controls(0).Name
          End If
      End Select
    Next ctl
    strOptionToggleLabels = strOptionToggleLabels & " "
    For Each ctl In ctlOptionGroup.Controls
      Select Case ctl.ControlType
        Case acLabel
          If InStr(" " & strOptionToggleLabels & " ", ctl.Name) = 0 Then
             FindOptionGroupLabel = ctl.Name
          End If
      End Select
    Next ctl
    Set ctl = Nothing
  End Function

This could probably be done with a single loop through the option group's Controls collection, but it's late! What's there seems pretty close to bullet-proof, not that anyone gives a rat's ass, of course! :)

Upvotes: 6