+ Reply to Thread
Results 1 to 33 of 33

When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I believe the code below is correct; it's derived from a Chip page. The problem is that I can run test1 to create click code for myButton1, no problem. Or I can run test2 to create click code for myButton2, fine. But I can't do BOTH. That is, not back to back: test_two_creations_always_goes_boom violently crashes every time.

    My theory is that there is a flawed incompatibility between ActiveX controls and interactive VBA code. I've encountered this when doing button creation, e.g.
    activesheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
    giving the lame message "can't enter break mode at this time" if you step or use breakpoints. I've also had problems when doing DAO ops from ActiveX buttons.

    Note this may be version specific; I'm on 2003.

    In order to reproduce, in a fresh book, first create two activeX command buttons. In 2003 that's View/Toolbar/Control Toolbox and drag command button to sheet (twice) to create them. Then in design mode, right click, properties, and name them myButton1 and myButton2.Note, this is all on the worksheet; no Forms. You might need to add a VBA reference to Microsoft VBA Extensibility. I recommend that you SAVE the file after creating the buttons, because you're about to go boom and have to rebuild them otherwise.

    Kind warning, again: don't have unsaved or corruptible workbooks open. Excel is going down if you test the above. (Maybe you won't crash. Maybe I'm just corrupt. But I do have that oh-so-rare condition right now where I reproduce the success every time and reproduce the failure every time, repeatedly.)

    So is there something I can do differently to avoid the crash, besides having to run the routines one at a time?
    Please Login or Register  to view this content.
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I ran all three test macros several times. No boom. Using Excel 2003 on Windows 10 with two ActiveX command buttons as you describe.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I don't think that that occurrence would be particularly surprising. Adding code at run time is really a developer aid and you shouldn't really expect users to execute it.

    Why not just add event handlers in the normal way and subscribe to the events? Or even better avoid using ActiveX controls on worksheets, use forms controls and just associate existing code

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Thanks Alpha for the report. Corruption is viciously difficult to diagnose but that could be in play here.

    Kyle, the reason for not doing it the "normal" way is that the buttons are new, newly created by code as well so there's no object to code for. More than that, though, the sheet itself doesn't exist until a few milliseconds before this code runs. Even if I put "pre-existing" code into Module1, I still need to add event code for Sheet2 after sheet2 is created, even if it's a single line call to the Module1 code.

    As to forms, my attitude has been, why add that layer for just a button or two? If I had labels and text boxes and dropdowns or radio buttons and I wanted to organize them all together in one place, okay, maybe add a form for that. (Yet actually all that input business could still be organized all together in one place just by designating one sheet for input instead of a form.) I'll add a form if I must resign to the reality that controls are more stable that way, if that's the case.

    A sheet button just seems to be the cleanest design for what I'm doing. I populate a new worksheet with data. The user looks at the data, and if user wants to react, user picks one of the 2 buttons over on the right, one to take action, and another to just quit. They've even got a shortcut accelerator so you page through the data and go Alt-G or Alt-X. Clean, simple. And the sheet is active - not the form - while the user is browsing the data, so they can page up and down. A [modal] form would just interfere with that, right? One way to address that might be to have a form available for whatever user responses; but ideally, that form would .Hide out of the way, unless .Show(n) ... by just pressing a button on the sheet! I just can't envision an interface that efficient that doesn't use a sheet control. (Unless, again, [dynamic] sheet controls are just unstable . Then I'll envision it just fine. )

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I think you've misunderstood me - im not advkcating a userform, a forms button. There are a couple of approaches to this, but the cleanest would be to add a forms button to the sheet (Not ActiveX and not a userform as I may have been unclear). With forms buttons, you simply assign them an existing macro by name - you can then use application.caller to work out which button called the macro.

    That way you can add as many buttons as you like to as many sheets and never have to add code.

    The other approach, or normal way is to use ActiveX controls as you have, but to create a class with the code you want to run in, you then pass the newly created buttons into the class and handle their events there. Again, no code adding at run time, honestly though the first option is simpler
    Last edited by Kyle123; 11-18-2017 at 03:31 PM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Another approach is to copy a template sheet that already has the ActiveX controls and code. Then just add the data.

    Myself, I prefer the Form-type controls as Kyle123 suggests.

  7. #7
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Sorry, I'm not sure I'm familiar with that. I know of 4 buttons: 1. buttons on forms from form toolbox, 2. buttons on sheets from sheet toolbox toolbar, 3. plain buttons (dim btn as Button), and 4. buttons created by .OLEObjects.Add(ClassType:="Forms.CommandButton.1" ... ). What I'm working with here is number 2. Number 4 is a weaker button than 2. I gather that 2 and 4 are both "ActiveX" and "OLEObjects"

    I'm familiar with forms' buttons having a macro assignment as a property, but I'm not familiar with using them except on a Form itself?

  8. #8
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Oh, it's from the "forms toolbar" for a sheet - yes, that may be superior. I'll look at that. Thanks.

  9. #9
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I think that is the plain button (Dim btn as Button, number 3 above). I dislike that, to my knowledge, you can't set the button color (you can set the font) or an accelerator (it has that property, but it is apparently nonfunctional). It is handy with the .OnAction property, a routine name. (I really want the accelerator. Of the four button types I mentioned before, number 2 has a usable accelerator property, and 3 and 4 don't.)
    Last edited by Oppressed1; 11-18-2017 at 05:06 PM.

  10. #10
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Just assign the accelerator to the macro rather than the buttons

  11. #11
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Do you mean control-shift, vs. alt?

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I don't understand what you're asking.

    're the colour, you could always use a shape instead, can be whatever colour you want, they have the onaction property too

  13. #13
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Anyone else want to take a crack at the original question? Like a lot of questions, contrasting approaches are suggested but the initial mystery remains...

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    As an aside, a working example that does what you need without hacks:
    Please Login or Register  to view this content.
    Uses Ctrl+F, Ctrl+G for options A and B
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    My conclusions, if anyone is interested:
    - On my XL2003 on XP, I crash under certain conditions using OLEObject operations described below. I expect that you will too though I can't say for certain (only 1 person reported trying it above; he said it did not crash).
    - This is apparently not due to corruption (unless the Excel installation itself is corrupt - unlikely). I reproduce at will with a new workbook.

    The condition producing failure is trying to do more than one of the following OLEObject operations without returning control to the user in between:
    Creating a button in code as described earlier above
    Creating button event code on a sheet module as shown above
    There may be other OLE operations, or broader cases than just listed; I don't have time to test them.

    So you can execute sub test1 in the original post above, and after it completes and returns control to the user, run test2. But executing test1:test2 bombs every time. If test3 created the button MyButton1 described earlier, you can run test3, and when control returns (when it's done), then test1, or test2, or test3 again, no problem. But you can't go test3:test1 or test3:test2 or any such pair; it you run
    Please Login or Register  to view this content.
    in my experience you will crash 100% of the time.

    ------
    One more "bug" (I think that's fair) occurs when you run such OLEObject code, even when you only do one OLEObject before returning control to the user, and do not crash. If you have a [static] global gbBoo as boolean, if you run the following (in conjunction with the original post)
    Please Login or Register  to view this content.
    Afterwards in the watch window or immediate window ?gbBoo will show true.

    Please Login or Register  to view this content.
    Afterwards in the watch window or immediate window ?gbBoo will show FALSE. In other words, by doing an OLEObject operation in code, a global variable has an incorrect value.

    I strongly suspect that OLEObject coding is faultily integrated with VBA. I hope that your mileage will vary.
    ------

    If anyone reads this in the future and reports their results here it might be beneficial. I cannot be certain that this malady is specific to my box and installation, or if it is version specific.
    Last edited by 6StringJazzer; 11-20-2017 at 03:03 PM. Reason: fixed code tags

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    This doesn't blow up for me either, though I do believe it's rather the wrong approach. As a general rule to hang your hat on, if you require users to add code at run time then you're probably doing it wrong
    Last edited by Kyle123; 11-20-2017 at 05:34 AM.

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    If you add an activex control to a worksheet, it becomes a member of that specific worksheet's interface, which causes a reset of the VB project and that's why your global variable loses its value. It is yet another reason to not use activex controls on worksheets.

    Regarding your earlier comment, numbers 2 and 4 are the same control, simply added manually versus in code.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  18. #18
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    xlnitwit: 1. Thank you for greatly that explanation. That also explains the irritating "can't enter break mode at this time." I'd rep you but it tells me I need to spread the love to others

    2. You also discouraged using ActiveX controls on worksheets, which I remembered I remember long ago debated by experts but had forgotten. You spurred me to restudy it. I'm looking at

    good one: https://answers.microsoft.com/en-us/...51ce7f1?auth=1 "Guidelines for When to Use ActiveX Control versus User Form Control - Microsoft Community"
    Walkenbach trumps all, but he doesn't provide an opinion: http://spreadsheetpage.com/index.php...on_worksheets/
    Jan Karel is against ActiveX on worksheets: http://www.jkp-ads.com/Articles/controls02.asp
    Highly educational, even if a Microsoft page!! Intriguing part begins: "A worksheet is a type of form...": https://support.office.com/en-us/art...ID=HA010237663 "Overview of forms, Form controls, and ActiveX controls on a worksheet - Excel"
    https://msdn.microsoft.com/en-us/vba...rols-on-sheets
    More expert discussion: https://stackoverflow.com/questions/...ol-in-excel-20

    https://www.google.com/search?source...trols+on+sheet "excel should I use activex controls on sheet - Google Search"

    3.
    Regarding your earlier comment, numbers 2 and 4 are the same control
    Actually they are different - see this

    Kyle, I don't know how you have an accelerator (shortcut) there (unless you used OnKey which IS well agreed to be radical/problematic, with exceptions.) My objective was to create a sheet button, assign or build code for it, and have an accelerator key (and I'll throw in, without modifying the Excel environment, e.g. putting shortcuts on the standard toolbar). Have you shown that that can be done without creating code? Moreover, as to the confident dismissal of the approach ("if you require users to add code at run time then you're probably doing it wrong"), my first inclination might be to agree with you, but that assertion might just be only in your mind. I don't think you'd go far if you contended that Chip Pearson gives bad advice . http://www.cpearson.com/Excel/vbe.aspx

  19. #19
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Chip doesn't advocate users running code that creates code - he shows how it can be done. There are indeed valid reasons for wanting to do so, but as a developer, not as a user.

    Kyle, I don't know how you have an accelerator (shortcut) there (unless you used OnKey which IS well agreed to be radical/problematic, with exceptions.) My objective was to create a sheet button, assign or build code for it, and have an accelerator key (and I'll throw in, without modifying the Excel environment, e.g. putting shortcuts on the standard toolbar). Have you shown that that can be done without creating code?
    Yes, did you try the file? And no, I haven't used OnKey, one can assign Keyboard shortcuts to macros through the GUI.

  20. #20
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Quote Originally Posted by Oppressed1 View Post
    3. Actually they are different - see this
    No, they aren't. It's the same control from the same dll. The fact that the UI doesn't seem to work properly for you doesn't actually change the fact that it's the same object. If you wanted to add an accelerator using the code in your other thread you should only need to add
    Please Login or Register  to view this content.
    I don't believe I have ever seen anyone sensible recommend using ActiveX controls if you can possibly get away with an alternative. The sheer frequency with which updates break them is enough to put me off.
    Last edited by xlnitwit; 11-20-2017 at 11:16 AM.

  21. #21
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Written another way, demonstrating that they are indeed the same thing (though admittedly in an OLEObject wrapper):

    Please Login or Register  to view this content.

  22. #22
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    The following refers to the "other" thread mentioned in my last post ("see this") and is collateral to this thread. I'll incorporate this there too.

    Thank you for the useful and on point response.
    Quote Originally Posted by xlnitwit View Post
    No, they aren't. It's the same control
    Okay, you're right. I'd still like to know if anyone knows how to add "Properties" to right click since it does not appear on the generated button (the only meaningful difference I now see).

    If you wanted to add an accelerator using the code in your other thread you should only need to add
    Please Login or Register  to view this content.
    Sorry, you're right, my mistake - setting the accelerator for either of those that way is no problem.

    I don't believe I have ever seen anyone sensible recommend using ActiveX controls if you can possibly get away with an alternative. The sheer frequency with which updates break them is enough to put me off.
    https://answers.microsoft.com/en-us/...51ce7f1?auth=1 and https://stackoverflow.com/questions/...ol-in-excel-20 indicate that there is no strict consensus. There are sensible MVPs that argue both sides.

    That's not to say your advice is not practical. I certainly am finding them to be problematic. But there are tradeoffs explained in those two links.

  23. #23
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I'm aware of the trade-offs.

    Personally I avoid activex for anything that needs to be distributed to anyone who is too far away to see- they are problematic enough that you will almost inevitably spend a fair amount of time troubleshooting, and that is much easier to do if you can be physically present. The way updates seem to be increasing in frequency and unreliability at the moment with the subscription model simply aggravates the issue.

    The only (former) MVP I see in those links that argues in favour of ActiveX is Chip Pearson and that was 7 years ago. I suspect his opinion might have wavered recently, if he still does any VBA work.

  24. #24
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    FYI, Jon Peltier: "Forms toolbar controls have been part of Excel since (I think) Excel 4. They are stable and easy to use. And they are old-fashioned. The Control Toolbox has newer ActiveX controls, introduced with Excel 97. These are fancier controls, with more formatting options, and somewhat more dynamic behavior. They often are blamed for erratic behavior, and only work in Windows versions of Excel. Seasoned Excel developers generally prefer the Forms controls and avoid the ActiveX controls. However, these controls can provide some slick effects." and in another context, "It has always been more reliable to use Forms menu controls rather than ActiveX controls."

  25. #25
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    So basically, avoid using ActiveX controls on worksheets where Forms controls are an option. Jon agrees with both xlnitwit and me, so give my approach a whirl and see how far it gets you

  26. #26
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    I disagree with making that a blanket statement. Basically

  27. #27
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Have you found an MVP advocating your approach yet? The blanket statement is, use forms controls or shapes unless there's a real need where these can't deliver what you want, but expect them to cause you problems - like shared workbooks come to think of it, but that's a can of worms for another day.

    There is never a one size fits all solution, but there are guidelines and recommendations for a reason. Ultimately, you can do whatever you feel like, people here are just trying to save you a headache down the road by advocating a certain approach that has come about through the experience of many others.

    I wish you the best of luck
    Last edited by Kyle123; 11-20-2017 at 01:01 PM.

  28. #28
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    This is my last remark to that, Kyle, after politely playing along with hijacking this thread far from the original post. I've been forthcoming to share that I have found a total of 2 MVPS that discourage ActiveX on sheets (and without overwhelming conviction either) - 2 out of, what, 100 plus "formers" Walkenbach, Pearson, Dave, etc.? You have an opinion, and thanks for sharing it. Your opinion has value, but you're not going to get a papal blessing that it's according-to-Hoyle gospel. FYI I lean to avoiding ActiveX in favor of the "old" form controls for reasons xlnitwit detailed, but it's not completely cut and dried. And BTW, numerous MVP sites show how to put Activex buttons and controls on worksheets - without any stated reservations. And I've been putting buttons on worksheets for many years without a glimmer of a problem; I just ran into an issue under a particular circumstance during button code generation, which is the genisys of this thread.

    Thanks for you and Alpha reporting that they didn't reproduce the crash, at least. Maybe one day someone will google and land here to address the crash aspect - if it happens to anyone else.

  29. #29
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Kyle, for what it's worth, I gave your approach a day of trial. I'm also a little less crabby today It does "feel" more "sane" than building code from code, and it is less of a PITA to not have to build code (vs. just assigning OnAction). It's still a tradeoff; the downside of a plain Button is its inability to utilize an alt-key accelerator (although you can attribute Control or Control-Shift, as you did in the alt-F8 options setting; I prefer the conventional approach of alt- shortcuts for specific workbooks), and the other more limited properties (e.g. I don't believe you can change the background color, unless you another layer of work via managing a Shape instead of a Button; or other formatting like word wrap don't seem configurable, etc.).

    Currently I'm 50-50 on whether to use an OLE button here. Noting that there are extremely few MVPs that will make a blanket statement "Avoid ActiveX" and possibly fewer that say "ActiveX is fine" on a worksheet, I don't think that any of them meant "NEVER" use "ANY" on a sheet. I'm confident that none of them said "Never click the control toolbox when you're on a sheet. It just causes problems" The little ActiveX command button is pretty innocuous, is more versatile than the plain button, and the only "downside" that I've heard anyone report was my own discovery in the original post here - and that was when I tried to do multiple (vs. single) button code generation, which I'll admit is exotic anyway; I'll confess that managing OLE objects in code is in some degree exotic, and as noted in the thread, can interfere with interactive functioning of VBA.

    So I'll opine that your approach is not without merit, but I'm only 50-50 on using it even in this complex application I'm working on. On simpler ones, I expect I'll always use ActiveX buttons. Thanks anyway for the suggestion and perspective.

    Finally, if anyone reads this thread in the future, and they can speak to the crash described in the original post, I'll be listening.

    P.S. to user AlphaFrog or anyone who did not reproduce: FWIW I tried both fresh XL03 and with SP3 - both crash, both on [non-virtual] XP, 100% reproducibly.

  30. #30
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    're adding another layer by using a shape, it's actually exactly the same as using a forms button. So that would give you the ability to customize the appearance as much as you like, admittedly though you're still not going to get an accelerator though and adding a "pressing" animation is more faff.

    Hope you settle on a solution that you're happy with

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

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Here's one that will:

    Avoid ActiveX controls on worksheets if you possibly can. That is a blanket statement, but it's not an edict, just advice. You take it or ignore it as you see fit.

    I disagree with your comment about "the only downside". Never mind the problem you are having here, or the longstanding and oft-discussed random resizing issues, or the fact they don't work at all on Macs, or the issues when you use multiple windows on a workbook; as has been alluded to here, there have been at least three occasions relatively recently where Office updates have rendered ActiveX controls on worksheets completely inert, as well as simultaneously causing compile issues with completely innocuous code simply because of their presence.

    Furthermore, I'd agree with Kyle that if you are writing code for an end user to run that itself writes code, it tends to indicate an inappropriate design and you're also completely at the mercy of their settings, which are often beyond their control too.

    Re the accelerator, you can use Application.Onkey to assign more or less any key combo to your macro.

    Purely my tuppence ha'penny worth.
    Remember what the dormouse said
    Feed your head

  32. #32
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Wow. That's pretty blanket. That sounds like "cmdButton_Click()" might best be removed from [some] designers' vocabulary - or for that matter any event code!

    "Mercy of their settings" - excellent point. I had not considered that. "tends to indicate inappropriate design" - errr, I explained the scenario earlier in the thread. (This is for a newly, dynamically created button.)

    As to freely using Onkey - well perhaps you're emboldened me to be more aggressive using it. I always sensed that it (and SendKeys) tended to indicate inappropriate design

    Forgive me, I'm not just being obstinate. I just think that some objections are tantamount to "don't even bother to open the control toolbox on a worksheet." That seems quite over the top, as opposed to saying something like that certain usage or manipulation of them in code can lead to problems. Maybe those controls belong in the category of "gateway drugs."

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

    Re: When I create VBA code via code generation, for 1 event, fine, but 2, crash boom

    Yes, it is blanket. It's meant to be. If you have any other practical option, don't use ActiveX controls on worksheets as they are not worth the headache.

    Forgive me, I'm not just being obstinate. I just think that some objections are tantamount to "don't even bother to open the control toolbox on a worksheet." That seems quite over the top, as opposed to saying something like that certain usage or manipulation of them in code can lead to problems
    Forgive me, but it really does seem like you are. I and others have listed a variety of problems with those controls. The Office update issues have caused problems all over the globe so no I'm not just saying that certain usage can cause problems: any usage can cause problems which are beyond your control. I don't really see that it's "over the top" to point out all the myriad problems that can occur with what you're doing.

    OnKey and SendKeys are nothing alike. There is nothing inappropriate about shortcut keys to run code.

    Anyway, I've said what I came to say, so I'm off again. Have fun.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Testing single code, work fine. Put multiple codes in one sheet, one code doesn't work.
    By MayDay1988 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2017, 06:14 PM
  2. Code works fine when stepping through code but Excel freezes if I run it without stepping
    By OldManExcellor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2016, 10:39 AM
  3. [SOLVED] VBA Code Change Event Code When 2 cells are selected
    By bjnockle in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-16-2014, 06:08 PM
  4. Help to create a worksheet selection change event code
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-09-2013, 08:46 AM
  5. Fine-tuning VBA code
    By smurray444 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2007, 11:49 AM
  6. Code help appreciated:Code runs fine
    By peter.thompson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2006, 10:03 PM
  7. [SOLVED] stubborn Excel crash when editing code with code, one solution
    By Brian Murphy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2005, 02:06 AM

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