Using Autoshapes as macro buttons
"Jay" wrote:
Hi Plum -
It's OK to use autoshapes or any shape to present the user with a visual que
to fire a macro. However, assignment of a macro to any shape (even if it's a
toolbar button or a button on a worksheet) needs to be handled with care or
the symptom you describe can and will occur.
It's not considered a 'bug', but Excel can 'lose track' of a macro that's
assigned to a button or shape. For example, if a worksheet containing a
shape (with and assigned macro) is copied to another workbook and then the
original workbook is deleted or its name is changed, your symptom will arise.
There are other causes for this problem, too, most of which occur
unexpectedly.
To avoid this, try putting the macro code in the worksheet module instead of
a standard module. VBA always looks in this module first when a macro path
is not specified, so you never have to worry about VBA not finding it (if you
can click the shape, the code always resides with the shape's underlying
worksheet - the shape and the code are always connected and move together so
moving, renaming, and deleting files has no negative impact).
A suggestion, too, is to use the "New" button when assigning the macro and
pasting your code into the macro name that is provided by vba (Sub
AutoShape1_Click, for example).
One final word of caution: shapes can be unpredictably renamed if you do a
lot of shape editing, adding shapes, grouping, ungrouping, deleting, etc. If
you do a lot of shape editing, just be aware that you might (might) have to
reassign the macro (using "New") if a shape_click suddenly stops working.
Rest assured, however, that once you're done editing and have your final
layout, the macro code and the shape will work properly.
---
Jay
"Plum" wrote:
Hi, I have created a model in Excel 2007 using autoshapes as macro buttons.
However, sometimes when I copy, rename or move the file, the buttons lose
their macro assignment. It doesn't seem to matter when I'm using it in 2003
or 2007. Is this a bug in Excel, or should I just not use autoshapes as
macro buttons?
|