Controlling dynamically created controls on a userform in VBA excel-CodePudding

I have created a multipage user form which dynamically populates with a set of identical frames and each of them has 2 option buttons based on previous user selections. I am trying to check if at least one of the option buttons is selected within each frame but don't seem to access the option buttons in code even through I know what their names will be. I will then be transferring the selection to a worksheet so need to be able to see what they have selected. Any help would be appreciated, I use VBA for excel infrequently so its always a struggle to be honest.

CodePudding user response：

It looks like your UI has a Page, which contains a Frame, which contains other Frames, each of which contains OptionButton's. The Page and Frame's are containers that contain other controls. If this is an accurate assessment you would reference an OptionButton like this:

If MultiPage1.Pages("Page1").Controls("Frame1").Controls("Frame2")
   .Controls("OptionButton2").Value = True Then

You could manually check each innermost frame like I did above, or set up looping to iterate through all the pages and frames.

CodePudding user response：

I'm getting closer, I've used this code of another post and changed it slightly while I trial what I am doing. Getting there slowly. :)

I'm not sure what some of the Class modules part is doing but its working.

Forms: Userform1




Option Explicit
 
Friend Sub OptionButtonSngClick(o As MSForms.OptionButton)
Dim cControlCheck As MSForms.Control
Dim cControlCheck1 As MSForms.Control
Dim cControlFrame As MSForms.Control
Dim strName As String


If Left(o.Name, 2) = "qN" Then
    o.BackColor = RGB(256, 0, 0)
ElseIf Left(o.Name, 2) = "qY" Then
    o.BackColor = RGB(0, 256, 0)
End If

For Each cControlCheck In UserForm1.Controls
    If TypeName(cControlCheck) = "Frame" Then
    For Each cControlCheck1 In Me.Controls(cControlCheck.Name).Controls
        If TypeName(cControlCheck1) = "OptionButton" Then
            If cControlCheck1 = False Then
                cControlCheck1.BackColor = RGB(240, 240, 240)
            End If
        End If
    Next
                
    End If
Next
      

End Sub

Friend Sub cmdCheck_Click()
Dim cControlCheck2 As MSForms.Control
Dim cControlCheck3 As MSForms.Control

Dim cCollection As Collection

Set cCollection = New Collection

For Each cControlCheck2 In UserForm1.Controls
    If TypeName(cControlCheck2) = "Frame" Then
    For Each cControlCheck3 In Me.Controls(cControlCheck2.Name).Controls
        If TypeName(cControlCheck3) = "OptionButton" Then
               cCollection.Add cControlCheck3
              
        End If
    Next
                
    End If
Next

If cCollection(1).Value = False And cCollection(2).Value = False Then

MsgBox ("Make a selection")


End If


End Sub

Class Module: OPtionButtonEvents

Option Explicit
 
  Private WithEvents ob As MSForms.OptionButton
  Private CallBackParent As UserForm1
  Private CallBackParent1 As UserForm1
 
Private Sub ob_Change()

End Sub

    Private Sub ob_Click()
    
    Call CallBackParent.OptionButtonSngClick(ob)
    
    End Sub

  Private Sub ob_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
       Call CallBackParent.OptionButtonDblClick(ob)
  End Sub
 
  Friend Sub WatchControl(oControl As MSForms.OptionButton, oParent As UserForm1)
       Set ob = oControl
       Set CallBackParent = oParent
  End Sub