Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
== 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don'tsink events..
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don'tsink events..
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL-2003 - Class Module - Controls declared "withEvents" don't sink events..
Thanks for your reply, Bob..
if you don't want a control array, what is the purpose of the event sink class? Because you can instantiate it more than once anywhere in your application.. Let's take the same example as yours, but without control arrays; I'll even add a third line and sink click events for the two buttons in the class module : === ' form module Option Explicit Private objLine1 As clsLine Private objLine2 As clsLine Private objLine3 As clsLine Private Sub UserForm_Initialize() Set objLine1 = New clsLine Set objLine2 = New clsLine Set objLine3 = New clsLine objLine1.SetCtls txtLine1_1, txtLine2_1, cmdLine1_1, cmdLine1_2 objLine2.SetCtls txtLine2_1, txtLine2_1, cmdLine1_1, cmdLine1_2 objLine2.SetCtls txtLine3_1, txtLine2_1, cmdLine1_1, cmdLine1_2 End Sub ==== ' clsLine Option Explicit Private WithEvents mtxt1 As MSForms.TextBox Private WithEvents mtxt2 As MSForms.TextBox Private WithEvents mcmd1 As MSForms.CommandButton Private WithEvents mcmd2 As MSForms.CommandButton ' this could also be replaced by 4 Property Set procs ' but since we always assign the four controls, why not use ' a single assignment routine ? Public Sub SetCtls(txt1 As MSForms.TextBox, txt2 As MSForms.TextBox, _ cmd1 As MSForms.CommandButton, cmd2 As MSForms.CommandButton) Set mtxt1 = txt1 'you don't have to match the form's names. Set mtxt2 = txt2 Set mcmd1 = cmd1 Set mcmd2 = cmd2 End Sub Private Sub mtxt1_Change() SetButtons End Sub Private Sub mtxt2_Change() SetButtons End Sub Private Sub mcmd1_Click() '... End Sub Private Sub mcmd2_Click() '... End Sub Private Sub SetButtons() mcmd1 = mtxt1.Value < 0 And mtxt1.Value < vbNullString mcmd2 = mtxt2.Value mtxt1.Value End Sub I think this method pros are : - you instantiate the class where you want it, as many times as you need - you dont have to "twist" you controls names to fit a given pattern - controls names are resolved at compile time instead of at run time - easier to read/debug Cons: - need more code lines in the calling module when the number of instances grows.. - ... (can't find others - do you ?) In an Access application, I once designed a "calendar" class, containing a textbox, a button and a calendar control popup form. The user can enter a date in the textbox, use arrow keys to change days, click on the button to pop the calendar control (which takes the textbox value on enter and sets it on exit) as he wish. This class can be used on any form.. and you don't need to have more than one instance on the same form (which is a requirement of a control array). Nevertheless, I learned something new with your method, and I can think of some situations where I can use it. Thanks again for your input ! Michel Bob Phillips a présenté l'énoncé suivant : 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 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 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 ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
There are no "BeforeCalculate" or "AfterCalculate" events | Excel Discussion (Misc queries) | |||
CommandBars("Worksheet Menu Bar").Controls("Tools").Enabled = Fals | Excel Programming | |||
Repost: ListBox Headings - customized! Class Module (warning: long post)" | Excel Programming | |||
Can ActiveX controls be "disabled" and "enabled"? | Excel Programming | |||
What events can be captured in a Class Module? | Excel Programming |