PDA

View Full Version : Dynamic OptionButton Validation



JimFl78
11-14-2007, 02:40 AM
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
11-14-2007, 05:36 AM
Don't create them on the fly, create them at design time, and hide/unhide as and when required.

JimFl78
11-14-2007, 06:39 AM
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
11-14-2007, 07:38 AM
what type of varible is cControl2? i have some ideas but i need to experiment befor i post them.

Bob Phillips
11-14-2007, 07:58 AM
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
11-14-2007, 08:07 AM
My cControl variable is decelared as


Dim cControl2 As Control

JimFl78
11-14-2007, 08:09 AM
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
11-14-2007, 08:12 AM
so are you runing into a problem where all the new buttons call the same code? or do they do nothing?

Bob Phillips
11-14-2007, 08:38 AM
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
11-14-2007, 09:25 AM
Jim

Could you please stick to the original thread?

JimFl78
11-14-2007, 10:58 AM
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)??


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


Please note the original OptionButtonEvents Class module still exists.

Bob Phillips
11-14-2007, 11:15 AM
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
11-14-2007, 12:09 PM
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
11-15-2007, 03:14 AM
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
11-15-2007, 07:16 AM
Something like:
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


should work, I think.

JimFl78
11-15-2007, 08:04 AM
Thanks alot Rory - thats seems to have done the trick..

So it seems to be just the


Set OBE = New OptionButtonEvents



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?

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



Many thanks for help on this.

rory
11-15-2007, 08:10 AM
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
11-21-2007, 04:52 AM
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
11-21-2007, 06:04 AM
Your class would need:
Private Sub ob_Click()
Call CallBackParent.OptionButtonClick(ob)
End Sub


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

JimFl78
11-21-2007, 07:07 AM
I tried something along those lines but left in the
ByVal Cancel As MSForms.ReturnBoolean
within my ob_click subroutine parentheses.

Thanks again Rory

rory
11-21-2007, 07:26 AM
No problem. You can get the correct procedure declaration in the class module by selecting 'ob' in the left dropdown and then the event you want in the right dropdown at the top of the main code pane in the VB Editor.