1

I have a Excel VBA Userform with two frames (Frame1 and Frame2) that I populate with a lot of Checkboxes depending on the amount of data when the form loads:

The interested code is:

Private Sub enterNewTextbox(number As Long, nameAdd As String, side As String)
    Dim boxTop As Long
    Dim a As Long
    Dim nameCB As String
    Dim checkBox As Control
    boxTop = 10 + (number * 18) - 18
    nameCB = side & number & "CB"
    If side = "Left" Then
        Set checkBox = Frame1.Controls.Add("Forms.CheckBox.1", nameCB)
    Else
        Set checkBox = Frame2.Controls.Add("Forms.CheckBox.1", nameCB)
    End If
    With checkBox
        .Value = False
        .Top = boxTop
        .Left = 12
        .Width = 190
        .Name = nameCB
        .Caption = nameAdd
    End With
End Sub

Now I would like to loop through all the controls again and get all the controls that was added to the left frame - something like this:

For Each ctrl In Me.Controls
If TypeName(ctrl) = "Checkbox" Then
   If **{find the name of the frame that the Checkbox is in}** = "Frame1" Then
       'Be awesome here
   End If
End If

But when I look in the Properties of the control I don't find any field to query that shows me what frame it is in.

Alfa Bravo
  • 1,961
  • 2
  • 25
  • 45

2 Answers2

2

Just use the Parent name. ctrl.Parent.Name

enter image description here

cyboashu
  • 10,196
  • 2
  • 27
  • 46
1

In general, the ctrl.Parent.Name works quite ok on a UserForm, however if the controls are on a worksheet like this:

enter image description here

Or like ActiveX elements:

enter image description here

Then the Parent of the shape refers to the worksheet name and you have to solve the coordinate system problem:

Sub TestMe()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
       Debug.Print shp.Name
       Debug.Print shp.Parent.Name
    Next shp
End Sub

returns:

Frame
Sheet1
Button 4
Sheet1

In programming schools, there are plenty of problems such as "Define whether two squares in the coordinate system overlap each other"

Every checkbox and every frame has the following 4 properties:

  • Top
  • Left
  • Width
  • Heigth

Knowing these, you could generate the Bottom and the Right positions:

  • Bottom = Top + Height
  • Right = Left + Width

Once knowing these, they overlap if the following condition is TRUE:

if (Frame.Left < Checkbox.Right && Frame.Right > Checkbox.Left &&
     Frame.Top > Checkbox.Bottom && Frame.Bottom < Checkbox.Top )

Determine if two rectangles overlap each other?

Vityata
  • 42,633
  • 8
  • 55
  • 100