Finding a named label in a form

FryerTuck

New Member
Joined
Jan 23, 2004
Messages
37
I have a complicated user form (pages on tabs on pages and multiple frames). Somewhere in there is a label I misnamed. I need to find the label and change it so I can use the name elsewhere. I have looked in the dropdown box of the properties window but can't find it anywhere, I have tried visual cues through coding (changing color, designating a caption, selecting) but haven't seen it, or get an error message that it is "of a type that does not accept the focus." Any ideas on finding it?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
How about listing the controls into the immediate window to see if it exist.

VBA Code:
Private Sub cmdCheckControls_Click()
Dim c As Control
For Each c In Me.Controls
     Debug.Print "Name - " & c.Name & " Type - " & TypeName(c)
Next
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Add a command button to your userform and place this code behind it
- it creates a sheet listing all your labels and where they are
- those not attributed to sub-objects are listed as " not identified"

VBA Code:
Private Sub CommandButton1_Click()
    Dim ws As Worksheet, Cel As Range, Ctrl As Control, pg As Object, labl As Object
    Set ws = Sheets.Add
    With ws.Range("A1:C1")
        .Value = Array("Name", "Caption", "Where")
        .EntireColumn.ColumnWidth = 40
    End With
On Error Resume Next    'lazy but avoids hiccups!

'loop Frames and list their labels
    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is MSForms.Frame Then
            For Each labl In Ctrl.Controls
                If TypeOf labl Is MSForms.Label Then Call WriteToSheet(Ctrl.Name, labl, ws)
            Next labl
        End If
    Next Ctrl
'loop MultiPages and list their lablels
    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is MSForms.MultiPage Then
            For Each pg In Ctrl.Pages
                For Each labl In pg.Controls
                    If TypeOf labl Is MSForms.Label Then Call WriteToSheet(Ctrl.Name & " " & pg.Name, labl, ws)
                Next labl
            Next pg
        End If
    Next Ctrl
'other labels
    For Each Ctrl In Me.Controls
        If WorksheetFunction.CountIf(ws.Range("A:A"), Ctrl.Name) = 0 Then
            If TypeOf Ctrl Is MSForms.Label Then Call WriteToSheet("not identified", Ctrl, ws)
        End If
    Next Ctrl
On Error GoTo 0
End Sub

Private Sub WriteToSheet(Desc As String, lbl As Object, ws As Object)
    ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3).Value = Array(lbl.Name, lbl.Caption, Desc)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,217
Messages
5,640,945
Members
417,181
Latest member
mgcorreia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top