Get worksheet buttons to execute code stored in an add-in module

  • Hey all,


    I accidentally posted in the wrong forum first: http://www.ozgrid.com/forum/showthread.php?t=167836


    I'm trying to change from mixed code and data to having code and data separate. Excel VBA has been awesome for what we're doing, but I'm starting to have issues when I need to update code with bug fixes etc. I'd like to use an excel add-in for the code on all my company computers and keep customer data in .xlsx formats.


    The issue I'm running into is that I've built and used several Button form controls on the excel worksheets i'm using. I'd like to keep using those buttons because all of my employees are trained to use this system and it's easier from a user standpoint than menus or ribbons. however, I don't know a way to connect the buttons to the code in the add-in. I've searched all over the interwebs and no luck yet. I can't be the first person with this issue as I've seen a lot of info on the "coders progression": Data > Data and Code mixed > Data and Code separate.


    The only thing I can think of so far is to add a snippet of code to each sheet which directs each button to the add-in subs, but then I can't keep my data in a .xlsx format and it's not truly separated code and data anymore. There must be an easy way!


    I'm using Office 2010 on Windows 7 32 bit and 64 bit platforms.
    cheers!
    Nate

  • Re: Get worksheet buttons to execute code stored in an add-in module


    I wonder whether I understood.


    why not write a macro


    Code
    sub test()
    application.run"<name of the macro in addin">
    end sub


    if this is not what you are thinking please some more information= better some trivial examples

  • Re: Get worksheet buttons to execute code stored in an add-in module


    Venkat, that would work but I'm trying to do this without a macro in the data sheet. I would like to only have code in the addin and not have to write any links in the data sheets.


    Is this possible?


    example: I have a "doIt" button in data123.xlsx as well as data456.xlsx and any variation of file names
    I have and addin called myAddin with subs mySetup() and myDoIt() etc.
    I'd like to have the code in mySetup() link to the button "doIt" if it exists in any of the data sheets (data123.xlsx or data345.xlsx)


    does my trivial example help?


    thanks for the reply!
    Nate

  • Re: Get worksheet buttons to execute code stored in an add-in module


    I found an answer to this question myself eventually. By creating a worksheet control button via VBA it is possible to set the .OnAction parameter to a script which is in the add-in rather than using the button1_click() event in the worksheet. See my code bellow which checks if the button exists and then creates the button with the correct link if the button does not exist.



    Cheers!
    Nate

Participate now!

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