Consulting

Results 1 to 16 of 16

Thread: Toolbar to Run From Addin

  1. #1
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location

    Toolbar to Run From Addin

    How do you open a form in an .xla file?

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    I solved the original question but that only let me have the Addin for that sheet.

    What I'm after is a customised menu with one button for my WorkSheet Manager addin that will always appear within my spreadsheet regardless of what workbook I decide to open and use it with.

    I don't want to have to put a little sub into each workbook before I can use my addin.

    My addins with just functions have worked okay; it's the use of a form that's getting to me.

  3. #3
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    In a module in your addin place the following code:


    Sub Auto_Open()
    Call Auto_Close() 'Delete it if it already exists
        Dim cb As CommandBar
        Dim cbBut As CommandBarButton
    'you can place it on a different toolbar by changing Standard to something else
        Set cb = Application.CommandBars("Standard")
    'the 4 in the line below is the position of the button on the toolbar
    '4 is the 4th button along i.e. to right of save button
        Set cbBut = cb.Controls.Add(msoControlButton, , , 4, True)
    'set the button features and the macro it should run
        With cbBut
            .FaceId = 59 'this is the icon
            .Style = msoButtonIcon 'display only the icon
            .TooltipText = "Runs the macro that opens the form"
            .OnAction = "MyFormMacro" 'the macro to run
    .Tag = "AbuButton" 'a string to identify it
        End With
    End Sub
    
    Sub Auto_Close()
    'delete the button
        On Error Resume Next
        Dim cb As CommandBar
        Dim cbBut As CommandBarButton
        Set cb = Application.CommandBars("Standard")
        Set cbBut = cb.FindControl(, , "AbuButton")
        cbBut.Delete
    End Sub
    
    Sub MyFormMacro()
    'this is the macro to show the form
    MyForm.Show
    End Sub
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  4. #4
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    Cheers Mark. That hit the spot.

    This addin is almost finished now for the Excel Add-in. It looks quite good and almost functions perfectly.

  5. #5
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    No problem.

    I must say you really sell it. It looks quite good and almost works LOL


    I've just been working on a class to handle styles. Not quite finished yet but should work out nicely I think.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  6. #6
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    Hey Mark,

    Nice piece of code.

    If you make use of the 'Temporary' parameter when you .Add() your controls, you don't have to bother worrying about the Auto_Close() proceedure...

    Mike
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    I just want to add that I've seen indications that MS really doesnt' want us to use auto_open and auto_close anymore....

    We should use:

    Workbook_Open
    Workbook_Close

    Document_Open
    Document_Close

    I'll find that reference if anyone is interested, but I believe it probably has to do with all those macro viruses that were created in these apps ages ago that used these modules to run the virus code...
    ~Anne Troy

  8. #8
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    I'm with Anne on this one. The various Auto routines are really only included for backwards compatability (that said, there have been rare instances where they work when the 'new' version doesn't ).

    For Chip Pearson's take on the position have a look at the article here:
    http://www.cpearson.com/excel/events.htm

    For those who don't wish to read the whole thing, the relevant part is ...
    Event Procedures And Auto_ Macros And On? Macros

    If you have programmed in previous versions of Excel, you are probably familiar with the Auto_Open and Auto_Close macros, which execute when the workbook is opened or closed, and with the OnEntry and OnSheetActivate properties, which execute when you enter a cell or worksheet. While these macros are still supported in Excel97, for compatibility with workbooks created in previous version, they have been replaced by event procedures.

    In general, you should not use the Auto_Open and Auto_Close macros in new workbooks. While they will still work as expected, you should become familiar with and learn to use the new event procedures. Of course, if you are developing a workbook that will be used by users with an earlier version of Excel, you cannot use event procedures and must use the Auto_Open and Auto_Close macros. Remember that when you open a workbook through VBA (with the Workbooks.Open method), the Auto_Open macro is not executed. You will have to use the RunAutoMacros method of the Workbook object to run it.

  9. #9
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    I use them out of habit really. There are occasions when I prefer them though:

    1. I can call auto_open by going to tools/macros/run if I wanted to reset something
    2. When opening a owkrbook through code you can choose whether the auto_macros will run or not whereas the workbook_open event will always run.

    As far as I'm concerned Microsoft can want what they like but as long as it works it doesn't really matter what you use.....

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  10. #10
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi Mark,

    Re 1.
    As far as I'm concerned events, such as Workbook_Open, should be used to call subs contained in general modules rather than contain actual chunks of code themselves. That way, there shouldn't be any trouble in calling subs as required.

    Re 2.
    We could always use:
    Sub Test()
        Application.EnableEvents = False
        Workbooks.Open FileName:="deleteme.xls"
        Application.EnableEvents = True
    End Sub
    That said, there's sometimes a lot of merit in the 'if it ain't broke, don't fix it!" approach.

  11. #11
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Richie, your point is quite valid, but I do still use Auto_Open() if only to not have to worry about Application.EnableEvents = False when opening with code.

    Just a personal preference I guess, but what I like about it is that I can export a fully auto run module for use in other projects, no other reason really.

  12. #12
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    I have noticed bugs in the Workbook_BeforeClose() Event not firing in XLA's within Excel 8.0 and 9.0. It's fine for Workbooks, however, and appears fixed as of Excel 10.0 for XLA's. But in some situations, Auto_Close() can actually be a more reliable technique. That said, I never use Auto_() procedures anymore, but I guess if I got stuck in a situation where an Event were not firing properly, of course I would use one instead...

    Mark, you can use Alt|Tools|Macros to run a Workbook_Open event; you just have to type "ThisWorkbook.Workbook_Open" (without the quotes, of course), which would run the Workbook_Open event for the ActiveWorkbook. (The "ThisWorkbook" part being the name of the class, and having nothing to do with where the code actually resides.) Or type "Book1!ThisWorkbook.Workbook_Open" if you need to specify which Workbook in which you want it to run...

    -- Mike
    Try out the [VBA] tags!
    Option Explicit, don't leave home without it...


  13. #13
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    Mark, you can use Alt|Tools|Macros to run a Workbook_Open event; you just have to type "ThisWorkbook.Workbook_Open" (without the quotes, of course),
    Ooh, that's far too long - too much scope for typos!
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  14. #14
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location

    Can you also...

    still modify the default XL workbook template? If the objective is to have a button on each worksheet, would that be an easier and more portable method?

    Just curious.

    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  15. #15
    This code is not working for me. My Add-In runs without executing the code. It works fine if I run the Sub manually. I'm using Excel 2003.

  16. #16
    VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Which code is not working and are you referencing activeworkbook or thisworkbook? If you want the addin to work on open workbooks you should use activeworkbook.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

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