Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub


When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to
pop, but nothing happens.

Any idea why it doesn't work ?
Thanks

== clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim objBtn As clsBtn
Set mcolEvents = New Collection

Set objBtn = New clsBtn
Set objBtn.mCmdOk = Me.ExistingCmdBtnOnThisForm
mcolEvents.Add objBtn
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Michel S." wrote in message
...
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub


When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to pop,
but nothing happens.

Any idea why it doesn't work ?
Thanks

Thanks for your reply Bob,

Andy Pope found where the error was in my code.. You may take a look
at my reply to him if you are interested.

My understanding is that the collection you suggest keeps the objBtn
object in scope once the Initialize routine finishes. Am I correct ?

If this collection is another way to make the link between the class
module and the form controls (instead of the Property Set prodedure you
removed) it completely new to me. I'd be interested to learn more; if
you happen to know where I can find additional information, I'd
appeciate you let me know.

Thanks again.

Bob Phillips a exposé le 2007-02-12 :
== clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim objBtn As clsBtn
Set mcolEvents = New Collection

Set objBtn = New clsBtn
Set objBtn.mCmdOk = Me.ExistingCmdBtnOnThisForm
mcolEvents.Add objBtn
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michel S." wrote in message
...
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to pop,
but nothing happens.

Any idea why it doesn't work ?
Thanks

I also found that error Michel, if you look you will have seen that I
removed the userform/class level declaration of the objBtn object (you
declared it as class and procedure level). You need the class instance, I
got this not by using your object, but by creating a collection. I did this
because I assumed that you would be adding more buttons to the event sink
(else why bother doing it?), so I set it up for you.

I am afraid I don't know of any definitive guide to this, but you might
find these posting I have made in the past useful, http://tinyurl.com/27cyuk

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Michel S." wrote in message
...
Thanks for your reply Bob,

Andy Pope found where the error was in my code.. You may take a look at
my reply to him if you are interested.

My understanding is that the collection you suggest keeps the objBtn
object in scope once the Initialize routine finishes. Am I correct ?

If this collection is another way to make the link between the class
module and the form controls (instead of the Property Set prodedure you
removed) it completely new to me. I'd be interested to learn more; if you
happen to know where I can find additional information, I'd appeciate you
let me know.

Thanks again.

Bob Phillips a exposé le 2007-02-12 :
== clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim objBtn As clsBtn
Set mcolEvents = New Collection

Set objBtn = New clsBtn
Set objBtn.mCmdOk = Me.ExistingCmdBtnOnThisForm
mcolEvents.Add objBtn
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Michel S." wrote in message
...
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to
pop, but nothing happens.

Any idea why it doesn't work ?
Thanks

I admit I paid more attention at the Collection than the change you
made to the declaration.

To answer your question, yes there will be more buttons, but there will
also be other related controls used in the same class.


To make a simple example, lets say I have a form with 5 lines
containing each : two textboxes, and two buttons. All lines are
identical except for the scope of the data they work on.

The rules are identical for each line; (ex: if the first textbox is
null or contains 0, the first button is disabled; if the second textbox
value is lower than the first one, the second button is disabled).

For all lines, the textbox validations are the same and the routine
called when the buttons are clicked on are the same.

For each line, I create an instance of the class and pass the four
corresponding controls reference. The class will set the buttons
"enabled" property according the textbox value and handle all the
events of these four controls.

My understanding is that the Collection would not be suitable here
because it is used to simulate a control array - many instances of the
same control - instead of a group of related controls.. Am I right ?

Thanks again.

Bob Phillips avait écrit le 2007-02-12 :
I also found that error Michel, if you look you will have seen that I
removed the userform/class level declaration of the objBtn object (you
declared it as class and procedure level). You need the class instance, I
got this not by using your object, but by creating a collection. I did this
because I assumed that you would be adding more buttons to the event sink
(else why bother doing it?), so I set it up for you.

I am afraid I don't know of any definitive guide to this, but you might
find these posting I have made in the past useful,
http://tinyurl.com/27cyuk

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michel S." wrote in message
...
Thanks for your reply Bob,

Andy Pope found where the error was in my code.. You may take a look at
my reply to him if you are interested.

My understanding is that the collection you suggest keeps the objBtn
object in scope once the Initialize routine finishes. Am I correct ?

If this collection is another way to make the link between the class
module and the form controls (instead of the Property Set prodedure you
removed) it completely new to me. I'd be interested to learn more; if you
happen to know where I can find additional information, I'd appeciate you
let me know.

Thanks again.

Bob Phillips a exposé le 2007-02-12 :
== clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim objBtn As clsBtn
Set mcolEvents = New Collection

Set objBtn = New clsBtn
Set objBtn.mCmdOk = Me.ExistingCmdBtnOnThisForm
mcolEvents.Add objBtn
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Michel S." wrote in message
...
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to
pop, but nothing happens.

Any idea why it doesn't work ?
Thanks

Michel,

You are right, the purpose of the collection is to create a control array.
But as I said before, if you don't want a control array, what is the purpose
of the event sink class?

In this situation, I would create two textbox collections, one for each
textbox column, and two corresponding sinks, and I would use a naming
convention that allied controls in a row.

Something like this

Userform

Option Explicit

Dim mColText1 As Collection
Dim mColText2 As Collection

Private Sub UserForm_Initialize()
Dim oEvents As clsEventSink
Dim i As Long

Set mColText1 = New Collection
Set mColText2 = New Collection

For i = 1 To 2 '5 in your case

Set oEvents = New clsEventSink
Set oEvents.mTextBox1 = Me.Controls("txtLine" & i & "_1")
mColText1.Add oEvents

Set oEvents = New clsEventSink
Set oEvents.mTextBox2 = Me.Controls("txtLine" & i & "_2")
mColText2.Add oEvents

Next i

End Sub

ClsEventSink

Option Explicit

Public WithEvents mTextBox1 As msforms.TextBox
Public WithEvents mTextBox2 As msforms.TextBox

Private Sub mTextBox1_Change()
Dim sLine As String
sLine = Mid(mTextBox1.Name, 4, Len(mTextBox1.Name) - 4) 'chop leading
txt and trailing num
UserForm1.Controls("cmd" & sLine & 1).Enabled = _
mTextBox1.Value < "" And mTextBox1.Value < 0
UserForm1.Controls("cmd" & sLine & 2).Enabled = _
UserForm1.Controls("txt" & sLine & 2).Value mTextBox1.Value
End Sub

Private Sub mTextBox2_Change()
Dim sLine As String
sLine = Mid(mTextBox2.Name, 4, Len(mTextBox2.Name) - 4) 'chop leading
txt and trailing num
UserForm1.Controls("cmd" & sLine & 1).Enabled = _
UserForm1.Controls("txt" & sLine & 1).Value < "" And _
UserForm1.Controls("txt" & sLine & 1).Value < 0
UserForm1.Controls("cmd" & sLine & 2).Enabled = _
UserForm1.Controls("txt" & sLine & 1).Value < mTextBox2.Value
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Michel S." wrote in message
...
I admit I paid more attention at the Collection than the change you made to
the declaration.

To answer your question, yes there will be more buttons, but there will
also be other related controls used in the same class.


To make a simple example, lets say I have a form with 5 lines containing
each : two textboxes, and two buttons. All lines are identical except
for the scope of the data they work on.

The rules are identical for each line; (ex: if the first textbox is null
or contains 0, the first button is disabled; if the second textbox value
is lower than the first one, the second button is disabled).

For all lines, the textbox validations are the same and the routine called
when the buttons are clicked on are the same.

For each line, I create an instance of the class and pass the four
corresponding controls reference. The class will set the buttons
"enabled" property according the textbox value and handle all the events
of these four controls.

My understanding is that the Collection would not be suitable here because
it is used to simulate a control array - many instances of the same
control - instead of a group of related controls.. Am I right ?

Thanks again.

Bob Phillips avait écrit le 2007-02-12 :
I also found that error Michel, if you look you will have seen that I
removed the userform/class level declaration of the objBtn object (you
declared it as class and procedure level). You need the class instance, I
got this not by using your object, but by creating a collection. I did
this because I assumed that you would be adding more buttons to the event
sink (else why bother doing it?), so I set it up for you.

I am afraid I don't know of any definitive guide to this, but you might
find these posting I have made in the past useful,
http://tinyurl.com/27cyuk

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Michel S." wrote in message
...
Thanks for your reply Bob,

Andy Pope found where the error was in my code.. You may take a look at
my reply to him if you are interested.

My understanding is that the collection you suggest keeps the objBtn
object in scope once the Initialize routine finishes. Am I correct ?

If this collection is another way to make the link between the class
module and the form controls (instead of the Property Set prodedure you
removed) it completely new to me. I'd be interested to learn more; if
you happen to know where I can find additional information, I'd
appeciate you let me know.

Thanks again.

Bob Phillips a exposé le 2007-02-12 :
== clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Dim mcolEvents As Collection

Private Sub UserForm_Initialize()
Dim objBtn As clsBtn
Set mcolEvents = New Collection

Set objBtn = New clsBtn
Set objBtn.mCmdOk = Me.ExistingCmdBtnOnThisForm
mcolEvents.Add objBtn
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Michel S." wrote in message
...
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to
pop, but nothing happens.

Any idea why it doesn't work ?
Thanks

Hi,

By using Dim in the userform initialize event you are actual creating an
instance local to that procedure rather than the instance to the
userform. Try this modification.

Private Sub UserForm_Initialize()
Set objBtn = New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

Cheers
Andy

Michel S. wrote:
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub


When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to
pop, but nothing happens.

Any idea why it doesn't work ?
Thanks



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thanks !

Obviously it's monday morning ! ;-)

This is the way I always did it in Access ; Bob Phillips shows another
method using a controls collection I didn't knew about.

Is there actually a difference between these methods in terms of
performance, readability, upward compatibility ?

Thanks again !

PS: I saw your replies in Google groups *before* they even show in my
newsreader - even with a refresh every five minutes - really amazing !

Andy Pope avait soumis l'idée :
Hi,

By using Dim in the userform initialize event you are actual creating an
instance local to that procedure rather than the instance to the userform.
Try this modification.

Private Sub UserForm_Initialize()
Set objBtn = New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

Cheers
Andy

Michel S. wrote:
Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to pop,
but nothing happens.

Any idea why it doesn't work ?
Thanks

I think the main difference is the collection will allow you to add more
controls whereas in my version you would need an object for each control.

Cheers
Andy

Michel S. wrote:
Thanks !

Obviously it's monday morning ! ;-)

This is the way I always did it in Access ; Bob Phillips shows another
method using a controls collection I didn't knew about.

Is there actually a difference between these methods in terms of
performance, readability, upward compatibility ?

Thanks again !

PS: I saw your replies in Google groups *before* they even show in my
newsreader - even with a refresh every five minutes - really amazing !

Andy Pope avait soumis l'idée :

Hi,

By using Dim in the userform initialize event you are actual creating
an instance local to that procedure rather than the instance to the
userform. Try this modification.

Private Sub UserForm_Initialize()
Set objBtn = New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

Cheers
Andy

Michel S. wrote:

Hello !

Given the following code :

' == clsBtn Class module

Private WithEvents mCmdOk AS MSForms.CommandButton

Public Property Set Ok (cmd AS MSForms.CommandButton)
Set mCmdOk = cmd
End Property

Private Sub mCmdOk_Click()
MsgBox "Ok button clicked"
End Sub

' == User Form
Private objBtn AS clsBtn

Private Sub UserForm_Initialize
Dim objBtn as New clsBtn
Set objBtn.Ok = Me.ExistingCmdBtnOnThisForm
End Sub

When I click on "Me.ExistingCmdBtnOnThisForm", I expect the MsgBox to
pop, but nothing happens.

Any idea why it doesn't work ?
Thanks




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info