Can events work with run-time-created Forms-controls?

[PSC]

Hi

I have a specific question about getting events to work with run-time created controls of VBA forms version 2.01

I've made a VBA form that has 2 static command buttons (OK and Cancel). The init-sub of the form creates x option buttons at run-time. The content of the option buttons are fecthed from an Excel Range.

What I now do is to scan through the Controls("name of the control") in a loop to figure out which option button is currently active when the user click on the OK-button.

It all works well, but I want to enable *_DblClick() event for all the option buttons so it is possible to make a choice by double-clicking on any of the option button.

If I create event-subs myself identical to what they would get at creating them at design, they will not trigger.

Does anyone know how the fetch double-click event on controls created by code at run-time? If you do, please, let me know.

 

[Legacy 98055]

ActiveControl.zip

To determine the activecontrol, download the example above. Note that CommandButton1's TakeFocusOnClick property is set to False. This property was set in design via properties.

As for you events question. Yes. Using a class module. For a specific example, please post your code...

 

[PSC]

Hi

The option buttons are created in a loop like this:

Set oBtn = Controls.Add("Forms.OptionButton.1", "obu" & j)
With oBtn
...properties are set here
End With

This sub is figuring out the current choice:

Private Sub cBtnOK_Click()
Dim i%
For i = 1 To msLastIndex
If Controls("obu" & i) Then
dlg1Option_.Return = i
Exit For
End If
Next i
Unload Me
End Sub

The calling sub then use a Select Case statement analyzing dlg1Option_.Return value to do what the user want.

It all works fine. My problem is to fetch DblClick event on these by code created option buttons.

If I create manually Subs like this...

Private Sub obu1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
...
End Sub

...they will not trigger. Forms seem only to link to these subs if the "obu1" option button exist when the code is compiled. Observe that these object are created at run-time, they do not exist when the code is compiled.

The only way to access these object seem to be through the Controls container. I do know there names.

But, the question remains, how do I fetch the double-click event on these by code created option buttons?

 

[Legacy 98055]

Shows one way of trapping the events of dynamically added controls...

WatchOptionButtonDblClick.zip

Code in a userform named, "MyUserform".
<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>
  
  <font color="#0000A0">Dim</font> OB_Coll <font color="#0000A0">As</font> Collection
  <font color="#0000A0">Dim</font> Pos <font color="#0000A0">As</font> <font color="#0000A0">Integer</font>
  
  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton1_Click()
       <font color="#0000A0">Dim</font> o <font color="#0000A0">As</font> MSForms.OptionButton
       <font color="#0000A0">Dim</font> OBE <font color="#0000A0">As</font> <font color="#0000A0">New</font> OptionButtonEvents
      
       <font color="#0000A0">If</font> OB_Coll <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font> <font color="#0000A0">Set</font> OB_Coll = <font color="#0000A0">New</font> Collection
      
       <font color="#0000A0">Set</font> o = Me.Controls.Add("Forms.OptionButton.1", , True)
       o.Caption = "Dynamically Added <font color="#0000A0">Option</font> Button" & CStr(Me.Controls.Count) - 2
       o.AutoSize = True
       o.Top = Pos
       OB_Coll.Add OBE
       <font color="#0000A0">Call</font> OBE.WatchControl(o, Me)
       Pos = Pos + o.Height + 4
      
      
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Friend</font> <font color="#0000A0">Sub</font> OptionButtonDblClick(o <font color="#0000A0">As</font> MSForms.OptionButton)
       MsgBox o.Name & " was just double-clicked..."
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>

Code in class module named, "OptionButtonEvents"
<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>
  
  <font color="#0000A0">Private</font> <font color="#0000A0">WithEvents</font> ob <font color="#0000A0">As</font> MSForms.OptionButton
  <font color="#0000A0">Private</font> CallBackParent <font color="#0000A0">As</font> MyUserform
  
  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> ob_DblClick(ByVal Cancel <font color="#0000A0">As</font> MSForms.ReturnBoolean)
       <font color="#0000A0">Call</font> CallBackParent.OptionButtonDblClick(ob)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Friend</font> <font color="#0000A0">Sub</font> WatchControl(oControl <font color="#0000A0">As</font> MSForms.OptionButton, oParent <font color="#0000A0">As</font> MyUserform)
       <font color="#0000A0">Set</font> ob = oControl
       <font color="#0000A0">Set</font> CallBackParent = oParent
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>

 

[PSC]

Hi these

Thanks for your time.

Now the first example is more elegant than using a loop, but I want the possibility to use ESC to cancel the form. That makes it difficult to use the ActiveControl at all. But of course it works fine otherwise.

The other code example is interesting but is not reqognized by Excel VBA. I'm not familiar with all of its syntax details like Friend. Otherwise it seems to collect the buttons object into a container and somehow link a kind of generic double-click event to them. I've only familiar with VBA.

Could you explane this a little, please?

 

[Legacy 98055]

"but I want the possibility to use ESC to cancel the form..." Set the "Cancel" property of your button to equal True...

It would be too longwinded to try and explain from ground zero. Basically, you are saving two references to two objects for each OptionButtonEvents custom object created. A reference to the dynamically added control and to the userform it'self. We use the reference to the control to watch it's events. The reference to the userform to callback the procedure, "Friend Sub OptionButtonDblClick". The callback is more of a convenience than a neccessity. If there were many calls, I would not use a callback. But how many times can a user possibly doubleclick a control. It's irrelevant in this case...

Please understand that there is no such thing as "Excel VBA". VBA is VBA. The Excel object model is simply refered to by default and is alltogether "other" than VBA...

 

[PSC]

Hi again Right-click

You are fast with the keyboard. I missed the additional details.

To be frank, I don't know much about classes. But I seem to understand that the class will connect an option button pointer to a class event that is connected to the form object itself, or something like that.

I will definitely investigate this further. One more question through. This Friend statement, I can't find any information about it. Is it possible to use with Excel VBA? If not, is it absolutely necessary.

Thanks again for your time.

 

[Legacy 98055]

From the help file. Select the "Friend" keyword and hit F1.

Friend

Modifies the definition of a procedure in a form module or class module to make the procedure callable from modules that are outside the class, but part of the project within which the class is defined. Friend procedures cannot be used in standard modules.

Syntax

[Private | Friend | Public] [Static] [Sub | Function | Property] procedurename

The required procedurename is the name of the procedure to be made visible throughout the project, but not visible to controllers of the class.

Remarks

Public procedures in a class can be called from anywhere, even by controllers of instances of the class. Declaring a procedure Private prevents controllers of the object from calling the procedure, but also prevents the procedure from being called from within the project in which the class itself is defined. Friend makes the procedure visible throughout the project, but not to a controller of an instance of the object. Friend can appear only in form modules and class modules, and can only modify procedure names, not variables or types. Procedures in a class can access the Friend procedures of all other classes in a project. Friend procedures don't appear in the type library of their class. A Friend procedure can't be late bound.

 

[PSC]

Hi again Tom

This form we're talking about is part of a non-profit application that must support Excel 97 and 2000 (or should I say VB 5). It's just a little piece of a huge system I've worked with for years. Therefore I'm making all the development in Excel 97 and then retesting everything with XL 2000, XP and 2003.

It has 0 classes. Probably because I don't know much about them or don't understand the beauty of them, or something...

I've only read one book about XL VBA resently and it wasn't useful at all. A lot of trivial and even some incorrect information to be frank. Mayby you can tip me about a good resent book for advanced VBA that explane classes and some. Well, just wondering.

Anyhow, I noticed that Excel XP knows the Friend statement. I don't know how friendly it is but it at least support "Friend". Mayby I could just define the sub as Public instead of "Friend".

 

[Legacy 98055]

Sure. Public would be fine. I just habitually use the least accessible scope. If you are using version 5, you will need to use public. I can't really recommend a single book and only have one that I use for reference. Excel 2002 VBA: Programmers Reference (Paperback)

Introduction To Class Modules In VBA See this nice page from Chip Pearson's website. He has a bunch of other great stuff there as well...