+ Reply to Thread
Results 1 to 14 of 14

Control Array WithEvents

  1. #1
    Registered User
    Join Date
    08-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Control Array WithEvents

    Hi,

    For test purposes I have a worksheet to which I dynamically add 3 buttons as a control array.

    Clicking a Checkbox creates the buttons, clicking a second checkbox hooks up the events (just a "Mouse Down" message), clicking a third checkbox deletes the buttons.

    All works fine (the status of the checkboxes is unimportant as they're only used to create the buttons etc for test purposes).

    But (there's always a but...) I have a fourth checkbox which creates buttons AND hooks up the events - but the events don't hook up for some reason - even though exactly the same code is running in the same scope as is run in the first two checkboxes.

    For the life of me I can't understand why one checkbox doesn't work! I'm using Excel 2010 but I don't think that it the cause at all... Any help appreciated.

    Regards,
    Brian

    Code below:

    Code in class module called cslActiveXEvents:

    Please Login or Register  to view this content.
    Worksheet (called "Debug") code:


    Please Login or Register  to view this content.
    Code in module called ButtonsModule:
    Please Login or Register  to view this content.
    Last edited by Deskmanbb; 08-23-2011 at 09:15 AM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Control Array WithEvents - funny behaviour

    Can you please add code tags to the code ?



  3. #3
    Registered User
    Join Date
    08-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Control Array WithEvents - funny behaviour

    Sorry... now added

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Control Array WithEvents - funny behaviour

    Since you 'dynamically' add controls, when will the connection to the class module be laid ?

    You only add 3 buttons in macro AddButtons()

    I'd prefer simpler code

    Please Login or Register  to view this content.
    I would also prefer to connect to the classmodule in the same macro that creates the controls.
    Last edited by snb; 08-22-2011 at 03:41 PM.

  5. #5
    Registered User
    Join Date
    08-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Control Array WithEvents - funny behaviour

    Hi snb,

    Thx for your help.

    The conection to the class module must be happening as the mouse down event does fire when the add buttons and attach events routines are called separately. I'm not expert enough to know if I'm doing something wrong there - but it works!


    In the real macro I will need to add an unknown number of buttons - perhaps 40~50.

    Re your code: I inserted it in my code module and commented out my equivalent but it doesn't add anything to the collection because the TypeName(ctl) line returns "OLEObject" not "Commandbutton" - maybe that's why the code I've copied off others takes the approach it does? I can't work with "OLEObject" because there will be other controls on the sheet that I don't want to hook up.

    Brian

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Control Array WithEvents - funny behaviour

    I don't think you already grasp what the code is doing:

    adapted code:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Control Array WithEvents - funny behaviour

    I amended the routine to match yours exactly - but it still doesn't add anything to the collection - inserting a msgbox (mcolevents.Count) just before the End Sub returns 0.

    If it helps, I've attached my workbook (in 97~2003 format but with the .xlt changed to xlsm to satisfy the uploader requirements) so you can see what I'm doing "works" - but it insists that the Add Button and Add Events routines are activated by separate checkboxes rather than from just one for some reason.

    Thanks again for your help.

    Brian
    Attached Files Attached Files
    Last edited by Deskmanbb; 08-22-2011 at 05:30 PM.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Control Array WithEvents

    It's all in the details
    CommandButton instead of Commandbutton

    Please Login or Register  to view this content.
    To make the code more robust:

    Please Login or Register  to view this content.
    Last edited by snb; 08-22-2011 at 05:40 PM.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Control Array WithEvents

    It won't work as one procedure because as soon as a procedure that adds activex controls ends, the vb project is reset and your variables get cleared. What you can do is use OnTime at the end of the routine that adds the controls to run the hook routine a second later.
    Remember what the dormouse said
    Feed your head

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Control Array WithEvents

    Maybe this is what you want. See the attachment.
    Attached Files Attached Files

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Control Array WithEvents

    Brian,
    For your test file, change the fourth checkbox to:
    Please Login or Register  to view this content.
    and you should see it work.

  12. #12
    Registered User
    Join Date
    08-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Control Array WithEvents

    RS and snb,

    Thank you so much for your help!

    RS: you've solved my initial problem,
    snb: you've improved my coding and understanding.

    I'd spent days playing around with this.

    Thumbs up to you guys.

    Cheers,

    Brian

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Control Array WithEvents

    Glad to help.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  14. #14
    Registered User
    Join Date
    08-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Control Array WithEvents

    Done! (Was trying to work that out as you posted lol)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1