Displaying Worksheet Macro Button

[Robert Morley]

Hi all,

I have a worksheet that my boss and I will be sharing. Sometimes, we want to display the data as is, other times, we want to run a
macro that will re-format it. I've got the macro all designed and functioning, but what I want to do now is to display a button for
the macro on the toolbar, but somehow make it only show up when that one Workbook is opened, regardless of who's opening it (i.e.,
if I send the file to my boss and she opens it, she should see the button). Is that possible?

If not, are there any other options you can suggest that would allow me to run a macro on-command, but be unobtrusive if we don't
want to run it?


Thanks,
Rob

 

[Brian Withun]

Hi all,

I have a worksheet that my boss and I will be sharing. Sometimes, we want to display the data as is, other times, we want to run a
macro that will re-format it. I've got the macro all designed and functioning, but what I want to do now is to display a button for
the macro on the toolbar, but somehow make it only show up when that one Workbook is opened, regardless of who's opening it (i.e.,
if I send the file to my boss and she opens it, she should see the button). Is that possible?

If not, are there any other options you can suggest that would allow me to run a macro on-command, but be unobtrusive if we don't
want to run it?

Thanks,
Rob

What you are talking about is an Attached Toolbar. You can create a
new toolbar and add a button to it that calls your macro when it is
clicked.

The new toolbar you create must be 'attached' to the current workbook
so that it will appear whenever anyone opens the workbook. (there is
an "Attach..." button on the 'Customize' toolbar dialog box)
Attaching toolbars to workbooks has its surprises, though. you may
want to read up on it so you understand how it is supposed to work
once you see how it *does* work.

I expect you'll want to write an OnClose() event macro which deletes
this new toolbar when your workbook closes. If you do not, it will
appear when the workbook is opened and then it will be there always.
This is because when you open a workbook with an attached toolbar,
that toolbar is copied into the default excel workspace. Deleting the
toolbar when the workbook is closed doesn't really delete the toolbar
altogether. It only deletes it from the default excel workspace. It
will appear again when your particular workbook is opened.

For a more thorough discussion, read:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarexcel9/html/xltoolbatt.asp

Brian Herbert Withun

 

[Robert Morley]

That's excellent, Brian. Thanks for the info!


Rob