Oleobject, Trigger Event, Not Firing

  • I have OLEObjects on an excel workbook that will not "fire" (Trigger an event sometimes). Just to be clear, each button is labelled (for example) as [Name][Number] and I've put a [Name][Number]_Click() event in the appropriate sheet code. The buttons work, but then randomly (it seems) stop working.


    One section of code, in particular, does causes this problem.



    When this code is executed, it removes all buttons whose names start with "ItemButton", then it recreates all the just-deleted "ItemButton"s and creates one more. The deleted-and-then-recreated buttons don't work, the new button does (as in clicking them does or does not trigger the _Click() event associated to each button).


    I think it has to do with the fact that a deleted button isn't replaced by a newly created button of the same name - that the new buttons don't absorb the same 'link' (?) to the click event.


    I've literally been trying to figure this ONE problem out for about 20 hours (because I have a lot of code and I don't want to change something fundamental like moving to a different non-OLEObject), so any help would be appreciated

  • Re: Oleobject, Trigger Event, Not Firing


    Hi There,


    I've encountered similar problems before.


    It's normally that the EnableEvents property isn't set to TRUE. Although this is the default for Excel the property can sometimes be upset by breaking your code halfway through or when your code hits an error.


    My way around it when I'm writing code is always to have a command button that fires;


    Code
    Application.EnableEvents = True


    Which I click before testing anything with triggers, and then I'm finished you can add the same line of code as the first line of code against all your command buttons in the workbook.


    That way you can guarantee, as long as the user has clicked a command button first, that the events should fire every time.


    I know that's not a specific solution to your problem but I hope it gets you in the right direction.


    Hope that helps,
    Ian

  • Re: Oleobject, Trigger Event, Not Firing


    Thank you very much for the reply, Ian. I would just like to specify that when the above code is run, (say I start with X "ItemButton" buttons, then) I end up with X+1 "ItemButtons" buttons. Buttons 1-X do not fire. Button X+1 does fire! So I think it has more to do with the connection between button-macro and that deleting the buttons and recreating the buttons somehow destroys this connection. I will, however, try your advice and, once again, thank you for the post/effort!

  • Re: Oleobject, Trigger Event, Not Firing


    Hi,


    I believe the click event for a button called ItemButton1 must be called ItemButton1_Click() therefor if this event exists and you then rename the button as ItemButton77 then it will no longer be associated with ItemButton1_Click()


    i.e. Your thought below is correct.


    Quote

    I think it has to do with the fact that a deleted button isn't replaced by a newly created button of the same name - that the new buttons don't absorb the same 'link' (?) to the click event.


    Don't understand why you are deleting all the buttons just to recreate them anyway?


    Eric

  • Re: Oleobject, Trigger Event, Not Firing


    Sorry I obviously read your post a bit quickly last time and tried to come up with too simple a solution.


    I think the key here is to not delte the buttons at all. I would just create an array that after stripping out the numbers, instead of deleting them, just finds the first number not already taken and uses that as the name for the new command button...


    That way you don't get any name clashes and you don't have to delete any which is what is probably causing the link to the macro to break down.


    Something like;



    That's not tested so it may not work 100% but it should get you in the right direction.


    Cheers,
    Ian

  • Re: Oleobject, Trigger Event, Not Firing


    Thanks again. I'm trying to convert your code into the context of the project I'm working on. Anyways..


    There still seems to be a little confusion. My reasoning for the deletion and then re-creation is that the code is sopposed to create a button for each instance of text in a box. If there is a "hole" (no text where there should be some), the code makes sure that any button that represents this "hole" is deleted and no recreated. So you can see how I thought it'd be easier just to delete it all and start over again. The REAL problem is that the newly creation IDENTICAL boxes are firing the same macro the prior (identical) version of the button had. If someone could tell me why that is or a fix for that I would be gracious.


    But as I said before, I think Ian's code is a nice temperary fix![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hey Ian, just wanted to let you know.. I still have the same problem when I don't delete the items... thanks for the effort though


    Does anyone know what's going on here?![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Hey Ian, just wanted to let you know.. I still have the same problem when I don't delete the items... thanks for the effort though


    Ian's code doesn't work, does anyone know what's going on here?!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!