Toolbar to Run From Addin

[SJ McAbney]

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

[SJ McAbney]

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.

[mark007]

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

[SJ McAbney]

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.

[mark007]

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.

[Mike_R]

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

[Anne Troy]

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...

[Richie(UK)]

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.

[mark007]

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.....

[Richie(UK)]

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.

[Insomniac]

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.

[Mike_R]

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

[mark007]

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!

[jamescol]

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

[bmocbarker]

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.

[lucas]

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.