How do I Dynamically identify the frame of an active control?

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
  1. 365
Platform
  1. Windows
I am using the age old loop through the controls ie.
Code:
                For Each cCont1 In Workshop.Frame2.Controls
                    If TypeName(cCont1) = "TextBox" Then
                        etc., etc.

My question is "How do I identify the frame that the control resides in?" I have 10 frames in frame2 and they all have identical parameters. Ultimately what I am attempting to do is to loop through each of the 10 frames to populate labels based on the textboxes in those same frames.

I know how to do it with folders and sub folders, but I am not sure how to do it with objects in a userform.

Any help is greatly appreciated.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,037
Office Version
  1. 365
Platform
  1. Windows
You should be able to use something like
Code:
 cCont.Parent.Name

That should return the Frame name or if it is a control on the Userform, it will return the name of the userform
 
Last edited:

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
  1. 365
Platform
  1. Windows
Tried .parent - gave me "object required".
Just tried .parent.name with the same results.

I don't think this should be this hard.

Thanks for trying. I am sure it is something like that. That is why they invented hammers, right?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,829
Office Version
  1. 2013
Platform
  1. Windows
Show us the script you just ran that gave you the error.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

"How do I identify the frame that the control resides in?"

If you have the name of the control, eg. Textbox1, then

Code:
wFrame = TextBox1.Parent.Name
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
  1. 365
Platform
  1. Windows
I already rewrote the version that gave the error using .parent.name.
For now I compromised by using this.
Code:
    Dim cCont As Control
    Dim cCont1 As Control
    
    For Each cCont In Workshop.Frame2.Controls
        If TypeName(cCont) = "Label" Then
            If cCont.Top = 144 Then
                cCont.Caption = ""
            End If
        End If
    Next cCont
    
    For Each cCont1 In Workshop.Frame2.Controls
        If TypeName(cCont1) = "TextBox" Then
            If cCont1.Text <> "" Then
                [B]For Each cCont In Workshop.Frame2.Controls[/B]
                    If TypeName(cCont) = "Label" Then
                        If cCont.Top = 144 Then
                            If cCont.Caption = "" Then
                                If cCont.Left = cCont1.Left Then
                                    cCont.Caption = Len(cCont1.Text)
                                    Exit For
                                End If
                            End If
                        End If
                    End If
                Next cCont
            End If
        End If
    Next cCont1

Optimally I would like to use the label that is positioned directly below each textbox. If I could get the parent statement to work for the imbedded for loop then I would be a happy man. You can see that this version is very weak and I could possibly get the wrong information in a label.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

You can relate the names of the labels and the text box.
For example Tb1 with Lb1, Tb2 with Lb2, Tb3 with Lb3. In this way it is not necessary to revise the Frame or the position of the Label, simply you know that the Tb1 corresponds with its Lb1.
The code would look like this:

Code:
    For i = 1 To 3
        If Me.Controls("Tb" & i).Value <> "" Then
            Me.Controls("Lb" & i).Caption = Me.Controls("Tb" & i).Value
        End If
    Next


You will only have to change once the name of all your label and texbox that are related.
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
  1. 365
Platform
  1. Windows
I think I understand your logic, however I would have to load 60 variables. I could use this method, but would like to learn how to do the other. I would rather work directly with the objects.

With the method that I posted I could get a good result if I could dynamically set and search in the same frame that the textbox is located. This would be a good thing to use in other situations.
 

MPW

Well-known Member
Joined
Oct 7, 2009
Messages
520
Office Version
  1. 365
Platform
  1. Windows
Thank you DanteAmor. Your first answer is what I needed.
If you have the name of the control, eg. Textbox1, then

Code:

wFrame = TextBox1.Parent.Name

I just replaced:
Code:
For Each cCont In Workshop.Frame2.Controls
with
Code:
For Each cCont In cCont1.Parent.Controls
This gave me what I needed, thanks again for you patience.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,027
Messages
5,526,329
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top