Dynamic OptionButton Validation

[JimFl78]

Hi,

I have created some optionButtons on the fly by extracting some data from my database query. If I run through my dataset and output some of the optionButtons as controls they all appear but I am having some trouble trying to find a solution as to how I can validate them. As they have been created at runtime it is difficult to set up an Onclick event.

Here is my original code:

 
Do While Not rst.EOF
 
 
        If n = 0 Then
            questionsTop = 25
        Else
            questionsTop = questionsTop + 65
        End If
 
        'set the controls here
        Set cFrame = Me.Controls.Add("Forms.Frame.1", "MyFrame", True)
 
        With cFrame
            .Width = 560
            .Top = questionsTop
            .Left = 20
            .ZOrder (1)
            .Caption = "Question " & (n + 1)
        End With
 
        Set qLab = cFrame.Controls.Add("Forms.label.1", "lab", True)
        With qLab
            .Width = 450
            .Height = 10
            .Top = 5
            .Left = 10
            .ZOrder (0)
            .Caption = CStr(rst(2))
        End With
 
 
        Set cControl2 = cFrame.Controls.Add("Forms.OptionButton.1", "opt1", True)
        With cControl2
            .GroupName = "q_" + CStr(rst(0))
            .Width = 150
            .Height = 20
            .Top = 0
            .Left = 475
            .ZOrder (0)
            .Value = 1
        End With
 
 
        Set cControl2 = cFrame.Controls.Add("Forms.OptionButton.1", "opt2", True)
        With cControl2
            .GroupName = "q_" + CStr(rst(0))
            .Width = 150
            .Height = 20
            .Top = 0
            .Left = 500
            .ZOrder (0)
            .Value = 0
        End With
 
      questionsTop = questionsTop      
    n = n + 1
    rst.movenext
    Loop

This code creates my options buttons in my db loop.

I found some code on the web that seemed to work for dynamic controls but would only work on one instance of a new created optionButton and not within the loop. I think it is because it doesnt know the difference between each of the optionButtons created in the loop.

Code created on my UserForm1:

Dim OB_Coll As Collection
  Dim Pos As Integer
 
  Private Sub cmdEvent_Click()
       'this works but need to apply to a my code
       Dim o As MSForms.OptionButton
       Dim OBE As New OptionButtonEvents
      '
       If OB_Coll Is Nothing Then Set OB_Coll = New Collection
 
        Set o = Me.Controls.Add("Forms.OptionButton.1", , True)
       o.Caption = "Dynamically Added Option Button" & CStr(Me.Controls.Count) - 2
       o.AutoSize = True
        o.Top = Pos
       OB_Coll.Add OBE
       Call OBE.WatchControl(o, Me)
       Pos = Pos + o.Height + 4
 
 
  End Sub
 
Friend Sub OptionButtonDblClick(o As MSForms.OptionButton)
       MsgBox o.Name & " was just double-clicked..."
  End Sub

Code created as Class Module OptionButtonEvents:

Option Explicit

 Private WithEvents ob As MSForms.OptionButton
  Private CallBackParent As UserForm1
 
  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

Is there an easier way to do this or should I try to modify this method to get it to work for me?

[Bob Phillips]

Don't create them on the fly, create them at design time, and hide/unhide as and when required.

[JimFl78]

Thanks but not really an option I think as I have a to download the latest version of questionnaire but synching with my database.

Can anybody else help?

[figment]

what type of varible is cControl2? i have some ideas but i need to experiment befor i post them.

[Bob Phillips]

Then you will need to link your new optionbuttons to the event handler class. Never tried that myself, but should be easy to test.

[JimFl78]

My cControl variable is decelared as

[VBA]
Dim cControl2 As Control[/VBA]

[JimFl78]

Hi XLD,

Anychance you can give me more of a clue how I would do that? The code is a bit beyond me at VBA and need to catch up quick.

If you could explain what you would do that would be great.

Many thanks

[figment]

so are you runing into a problem where all the new buttons call the same code? or do they do nothing?

[Bob Phillips]

Anychance you can give me more of a clue how I would do that? The code is a bit beyond me at VBA and need to catch up quick.

If you could explain what you would do that would be great.

As I said, I haven't done it myself, but I see a way in conecpt at least.

Do you have soome code to linke any buttons to that event handler class, because we would need to work on that.

[Norie]

Jim

Could you please stick to the original thread?

[JimFl78]

Apologies Norie I thought this was an independant forum and really need to get this resolved. Please note I have not posted on this forum before only on Dbforum.

Yes Figment - the problem occurs when I try to apply the cmdEvent_Click/OptionButtonDblClick event code and OptionButtonEvents Class Module to my version.

I think the problem occurs because the WatchControl command is referencing a single Object o rather than a set of objects set with a different name.

Is there a way I can modify the existing code so that it refers to the OptionButtons name rather than the object (for example q1_1)??

[vba]
Dim cFrame As Control
Dim cControl2 As Control
Dim OBE As New OptionButtonEvents
If OB_Coll Is Nothing Then Set OB_Coll = New Collection

'missing connect and call to db etc here

Do While Not rst.EOF

If n = 0 Then
questionsTop = 25
Else
questionsTop = questionsTop + 65
End If

'set the controls here
Set cFrame = Me.Controls.Add("Forms.Frame.1", "MyFrame", True)

With cFrame
.Width = 560
.Top = questionsTop
.Left = 20
.ZOrder (1)
.Caption = "Question " & (n + 1)
End With

Set cControl2 = cFrame.Controls.Add("Forms.OptionButton.1", "q1_" + CStr(rst(0)), True)
With cControl2
.GroupName = "q_" + CStr(rst(0))
.Width = 150
.Height = 20
.Top = 0
.Left = 475
.ZOrder (0)
End With

OB_Coll.Add OBE
Call OBE.WatchControl(cControl2, Me)

rst.movenext
Loop

Friend Sub OptionButtonDblClick(o As MSForms.OptionButton)
MsgBox o.Name & " was just double-clicked..."
End Sub
[/vba]

Please note the original OptionButtonEvents Class module still exists.

[Bob Phillips]

This is all getting confusing, how this code fits in with the other.

Just post a workbook, and expalin what steps we need to follow, what we can expect to see, and what you want to see.

[Norie]

Jim

I'm not sure what you mean by an 'independent' forum.

All I was trying to say was that if you already have a thread stick to it.

Having multiple threads for the same thing just confuses people and if you stick to the original thread then they can see what's been suggested so far.

Also if what's been suggested doesn't work it's best to post in the original thread explaining why.

[JimFl78]

so are you runing into a problem where all the new buttons call the same code? or do they do nothing?

Yes nothing happens when I place the OBE.WatchControl into my code above and it calls the same code. I was hoping that I would be able to ammend the class procedure so that it could work each of the instance names for example.


Sorry if my code doesnt work directly for you - I have simplified the example so that it works from just a single form ("UserForm1") and the class Module ("OptionButtonEvents").

Here is the code - you will need to create two buttons on UserForm1
cmdGetQuestions
cmdEvent

but everything else works dynamically.

Code for UserForm1

Option Explicit
 
  Dim OB_Coll As Collection
  Dim Pos As Integer
 
  Private Sub cmdEvent_Click()
       'this works but need to apply to a my code
       Dim o As MSForms.OptionButton
       Dim OBE As New OptionButtonEvents
      '
       If OB_Coll Is Nothing Then Set OB_Coll = New Collection
 
        Set o = Me.Controls.Add("Forms.OptionButton.1", , True)
       o.Caption = "Dynamically Added Option Button" & CStr(Me.Controls.Count) - 2
       o.AutoSize = True
        o.Top = Pos
       OB_Coll.Add OBE
       Call OBE.WatchControl(o, Me)
       Pos = Pos + o.Height + 4
 
 
  End Sub
 
Private Sub cmdGetQuestions_Click()
    Dim n As Integer
    Dim r As Integer
 
 
    Dim cControl As Control
 
    Dim OBE As New OptionButtonEvents
    If OB_Coll Is Nothing Then Set OB_Coll = New Collection
    r = 1
 
    For n = 0 To 5
 
        Set cControl = Me.Controls.Add("Forms.OptionButton.1", "q1_" + CStr(n), True)
        With cControl
            .GroupName = "q_" + CStr(n)
            .Width = 20
            .Height = 20
            .Top = Pos
            .Left = 10
            .ZOrder (0)
        End With
 
 
        Pos = Pos + cControl.Height + 4
 
        OB_Coll.Add OBE
        Call OBE.WatchControl(cControl, Me)
 
 
    n = n + 1
    Next n
 
 
End Sub
Friend Sub OptionButtonDblClick(o As MSForms.OptionButton)
       MsgBox o.Name & " was just double-clicked..."
  End Sub

Code for class Module OptionButtonEvents:

Option Explicit
 
  Private WithEvents ob As MSForms.OptionButton
  Private CallBackParent As UserForm1
 
  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

This code will hopefully show you what I am struggling with. The cmdEvent_Click event works correctly but I need to get it to work for my loop example in the cmdGetQuestions_Click event.

Many thanks for your help on this issue.

[rory]

Something like:
[VBA] Dim cControl2 As MSForms.OptionButton
Dim OBE As OptionButtonEvents
'
If OB_Coll Is Nothing Then Set OB_Coll = New Collection

'... other code here

Do While Not rst.EOF


If n = 0 Then
questionsTop = 25
Else
questionsTop = questionsTop + 65
End If

'set the controls here
Set cFrame = Me.Controls.Add("Forms.Frame.1", "MyFrame", True)

With cFrame
.Width = 560
.Top = questionsTop
.Left = 20
.ZOrder (1)
.Caption = "Question " & (n + 1)
End With

For i = 1 To 2
Set OBE = New OptionButtonEvents
Set cControl2 = cFrame.Controls.Add("Forms.OptionButton.1", "opt" & n, True)
With cControl2
.GroupName = "q_" + CStr(rst(0))
.Width = 150
.Height = 20
.Top = 0
.Left = 450 + i * 25
.ZOrder (0)
.Value = 2 - n
End With
OB_Coll.Add OBE
Call OBE.WatchControl(cControl2, Me)
Next n
questionsTop = questionsTop
n = n + 1
rst.movenext
Loop
[/VBA]

should work, I think.

[JimFl78]

Thanks alot Rory - thats seems to have done the trick..

So it seems to be just the

[vba]
Set OBE = New OptionButtonEvents

[/vba]

inside my loop that has resolved this.

When I am using optionButtons as a group of 2 and looping. I need to set up three OptionButtonEvents objects for each of them to call the function.
Is this best practise?

[vba]Private Sub cmdGetQuestions_Click()
Dim n As Integer
Dim r As Integer
Dim OBE As New OptionButtonEvents
Dim OBE2 As New OptionButtonEvents
Dim OBE3 As New OptionButtonEvents

Dim cControl As MSForms.OptionButton


If OB_Coll Is Nothing Then Set OB_Coll = New Collection
r = 1

For n = 0 To 5


Set cControl = Me.Controls.Add("Forms.OptionButton.1", "q1_" + CStr(n), True)
With cControl
.GroupName = "q_1" + CStr(n)
.Width = 50
.Height = 20
.Top = Pos
.Left = 10
.Caption = "Option 1"
.ZOrder (0)
End With

Set OBE = New OptionButtonEvents
OB_Coll.Add OBE
Call OBE.WatchControl(cControl, Me)

Pos = Pos + cControl.Height + 15

Set cControl = Me.Controls.Add("Forms.OptionButton.1", "q2_" + CStr(n), True)
With cControl
.GroupName = "q_1" + CStr(n)
.Width = 50
.Height = 30
.Top = Pos + 2
.Left = 10
.Caption = "Option 2"
.ZOrder (0)
End With

Set OBE2 = New OptionButtonEvents
OB_Coll.Add OBE2
Call OBE2.WatchControl(cControl, Me)

Pos = Pos + cControl.Height + 15


n = n + 1
Next n


End Sub

[/vba]

Many thanks for help on this.

[rory]

You can just re-use the OBE variable each time as long as you instantiate it as a New OptionButtonEvents object each time you use it and add it to the collection; there is no need for OBE, OBE2 and OBE3.

[JimFl78]

Here is the code - you will need to create two buttons on UserForm1
cmdGetQuestions
cmdEvent

but everything else works dynamically.

Code for UserForm1

Option Explicit
 
  Dim OB_Coll As Collection
  Dim Pos As Integer
 
  Private Sub cmdEvent_Click()
       'this works but need to apply to a my code
       Dim o As MSForms.OptionButton
       Dim OBE As New OptionButtonEvents
      '
       If OB_Coll Is Nothing Then Set OB_Coll = New Collection
 
        Set o = Me.Controls.Add("Forms.OptionButton.1", , True)
       o.Caption = "Dynamically Added Option Button" & CStr(Me.Controls.Count) - 2
       o.AutoSize = True
        o.Top = Pos
       OB_Coll.Add OBE
       Call OBE.WatchControl(o, Me)
       Pos = Pos + o.Height + 4
 
 
  End Sub
 
Private Sub cmdGetQuestions_Click()
    Dim n As Integer
    Dim r As Integer
 
 
    Dim cControl As Control
 
    Dim OBE As New OptionButtonEvents
    If OB_Coll Is Nothing Then Set OB_Coll = New Collection
    r = 1
 
    For n = 0 To 5
 
        Set cControl = Me.Controls.Add("Forms.OptionButton.1", "q1_" + CStr(n), True)
        With cControl
            .GroupName = "q_" + CStr(n)
            .Width = 20
            .Height = 20
            .Top = Pos
            .Left = 10
            .ZOrder (0)
        End With
 
 
        Pos = Pos + cControl.Height + 4
         Set OBE = New OptionButtonEvents 
        OB_Coll.Add OBE
        Call OBE.WatchControl(cControl, Me)
 
 
    n = n + 1
    Next n
 
 
End Sub
Friend Sub OptionButtonDblClick(o As MSForms.OptionButton)
       MsgBox o.Name & " was just double-clicked..."
  End Sub

Code for class Module OptionButtonEvents:

Option Explicit
 
  Private WithEvents ob As MSForms.OptionButton
  Private CallBackParent As UserForm1
 
  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

Looking back at this simple example - we solved this by setting the OBE object. It works when you double click on the optionButton becuase it is calling the doubleclick event.

Does anybody know how I would modify the code in the class module and the form to pass a single click event rather than double click? I have tried but have been unsuccessful so far.

[rory]

Your class would need:
[VBA] Private Sub ob_Click()
Call CallBackParent.OptionButtonClick(ob)
End Sub
[/VBA]

and your form needs:
[VBA]Friend Sub OptionButtonClick(o As MSForms.OptionButton)
MsgBox o.Name & " was just clicked..."
End Sub
[/VBA]

[JimFl78]

I tried something along those lines but left in the
[VBA]ByVal Cancel As MSForms.ReturnBoolean[/VBA]
within my ob_click subroutine parentheses.

Thanks again Rory