Looping selected controls on a designer

alexofrhodes

New Member
Joined
Sep 24, 2020
Messages
24
Office Version
  1. 2021
Platform
  1. Windows
Hi all. I have the following code to get the selected controls on a userform's designer module.
The problem is that some controls are within a frame,
in which case the frame itself is added to the collection instead of the selected controls inside it.
I expect a similar problem to occur when intending to loop selected controls inside a MultiPage.
Any insight?

VBA Code:
'needs reference to Microsoft Visual Basic for Applications Extensibility 5.3

Public Sub TestSelectedControls()
    'go to a userform and call this from the immediate window
    dim ctl as MSForms.Control
    for each ctl in SelectedControls(Application.VBE.SelectedVBComponent)
        debug.print ctl.name
    next
End Sub

Function SelectedControls(FormModule as vbComponent) As Collection
    Dim ctl    As control
    Dim out As New Collection
    For Each ctl In FormModule.Designer.Selected
        out.Add ctl
    Next ctl
     set SelectedControls=out
End Function
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Where you have

VBA Code:
For Each ctl In FormModule.Designer.Selected
you could insert an IF block
VBA Code:
If TypeName(ctl) = "Frame" Then
and then get the frame controls count with ctl.controls.count
That count would be zero based. At this point you could loop over the frame controls (For i = 0 to # after you get the count, or simply For i =0 To ctl.controls.count) and do whatever as far as adding to your collection.
If you want to add anything that's not a frame then in the loop you'd add ctl.Controls(i) if the TypeName is not "Frame". If you want to add option buttons but not checkboxes, then you'd test the TypeName of control(i). If you'd rather use the control type enumeration then swap TypeName with VarType. A command button would be 11.
Of course, if the control is not a frame you just add it to the collection as you are already doing.
 
Upvote 0
Edit to prior post - I see now that without any sort of test for selection (or not) my suggestion will add the frame controls to the collection regardless. If that's not wanted, then I guess it needs to be modified to add only selected controls. Not sure how at this point.
 
Upvote 0
This worked for me to exclude Frames and MultiPages
VBA Code:
Option Explicit

Public Sub TestSelectedControls()
    'go to a userform and call this from the immediate window
    Dim ctl As MSForms.Control
    For Each ctl In SelectedControls(Application.VBE.SelectedVBComponent)
        Debug.Print "Selected Ctrl: " & ctl.Name, "Parent: " & ctl.Parent.Name
    Next
End Sub

Function SelectedControls(FormModule As Object) As Collection
    Dim ctl    As MSForms.Control
    Dim out As New Collection    
    For Each ctl In FormModule.Designer.Controls
        If InStr("FrameMultiPage", TypeName(ctl)) = 0& And ctl.InSelection Then
            out.Add ctl
        End If
    Next ctl
    Set SelectedControls = out
End Function
 
Upvote 0
Solution
Jaafar thank you, it was .InSelection that I didn't know of.
Thank you Micron.
 
Upvote 0

Forum statistics

Threads
1,224,223
Messages
6,177,251
Members
452,765
Latest member
Erka Gizli

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
Back
Top